/*
 * ER/Studio 8.0 SQL Code Generation
 * Company :      Wisconsin Historical Society
 * Project :      PeDALS_DLB_Modified.DM1
 * Author :       whsrepos
 *
 * Date Created : Friday, July 24, 2009 09:03:54
 * Target DBMS : Microsoft SQL Server 2005
 */

USE PeDALS_DLB
go
IF OBJECT_ID('dbo.DigitalFormat_Component_FK1') IS NOT NULL
ALTER TABLE Component
DROP CONSTRAINT DigitalFormat_Component_FK1
go

IF OBJECT_ID('dbo.Hardware_Component_FK1') IS NOT NULL
ALTER TABLE Component
DROP CONSTRAINT Hardware_Component_FK1
go

IF OBJECT_ID('dbo.OperatingSystem_Component_FK1') IS NOT NULL
ALTER TABLE Component
DROP CONSTRAINT OperatingSystem_Component_FK1
go

IF OBJECT_ID('dbo.Software_Component_FK1') IS NOT NULL
ALTER TABLE Component
DROP CONSTRAINT Software_Component_FK1
go

IF OBJECT_ID('dbo.DigitalFormatRegistry_DigitalFormat_FK1') IS NOT NULL
ALTER TABLE DigitalFormat
DROP CONSTRAINT DigitalFormatRegistry_DigitalFormat_FK1
go

IF OBJECT_ID('dbo.DigitalFormatRegistryRole_DigitalFormatRegistry_FK1') IS NOT NULL
ALTER TABLE DigitalFormatRegistry
DROP CONSTRAINT DigitalFormatRegistryRole_DigitalFormatRegistry_FK1
go

IF OBJECT_ID('dbo.DigestAlgorithm_Item_FK1') IS NOT NULL
ALTER TABLE Item
DROP CONSTRAINT DigestAlgorithm_Item_FK1
go

IF OBJECT_ID('dbo.DigitalFormat_Item_FK1') IS NOT NULL
ALTER TABLE Item
DROP CONSTRAINT DigitalFormat_Item_FK1
go

IF OBJECT_ID('dbo.FilingUnit_Item_FK1') IS NOT NULL
ALTER TABLE Item
DROP CONSTRAINT FilingUnit_Item_FK1
go

IF OBJECT_ID('dbo.FK_Item_ItemLocation') IS NOT NULL
ALTER TABLE Item
DROP CONSTRAINT FK_Item_ItemLocation
go

IF OBJECT_ID('dbo.Hardware_Item_FK1') IS NOT NULL
ALTER TABLE Item
DROP CONSTRAINT Hardware_Item_FK1
go

IF OBJECT_ID('dbo.OperatingSystem_Item_FK1') IS NOT NULL
ALTER TABLE Item
DROP CONSTRAINT OperatingSystem_Item_FK1
go

IF OBJECT_ID('dbo.Series_Item_FK1') IS NOT NULL
ALTER TABLE Item
DROP CONSTRAINT Series_Item_FK1
go

IF OBJECT_ID('dbo.Software_Item_FK1') IS NOT NULL
ALTER TABLE Item
DROP CONSTRAINT Software_Item_FK1
go

IF OBJECT_ID('dbo.FK_ItemDate_Item') IS NOT NULL
ALTER TABLE ItemDate
DROP CONSTRAINT FK_ItemDate_Item
go

IF OBJECT_ID('dbo.FK_ItemDate_Qualifier') IS NOT NULL
ALTER TABLE ItemDate
DROP CONSTRAINT FK_ItemDate_Qualifier
go

IF OBJECT_ID('dbo.FK_ItemKeyword_Item') IS NOT NULL
ALTER TABLE ItemKeyword
DROP CONSTRAINT FK_ItemKeyword_Item
go

IF OBJECT_ID('dbo.FK_ItemOtherIdentifier_Item') IS NOT NULL
ALTER TABLE ItemOtherIdentifier
DROP CONSTRAINT FK_ItemOtherIdentifier_Item
go

IF OBJECT_ID('dbo.FK_ItemOtherIdentifier_Qualifier') IS NOT NULL
ALTER TABLE ItemOtherIdentifier
DROP CONSTRAINT FK_ItemOtherIdentifier_Qualifier
go

IF OBJECT_ID('dbo.FK_ItemParty_Item') IS NOT NULL
ALTER TABLE ItemParty
DROP CONSTRAINT FK_ItemParty_Item
go

IF OBJECT_ID('dbo.FK_ItemParty_Qualifier') IS NOT NULL
ALTER TABLE ItemParty
DROP CONSTRAINT FK_ItemParty_Qualifier
go

IF OBJECT_ID('dbo.DigitalSignatureKey_ItemSignature_FK1') IS NOT NULL
ALTER TABLE ItemSignature
DROP CONSTRAINT DigitalSignatureKey_ItemSignature_FK1
go

IF OBJECT_ID('dbo.FK_ItemSignature_Item') IS NOT NULL
ALTER TABLE ItemSignature
DROP CONSTRAINT FK_ItemSignature_Item
go

IF OBJECT_ID('dbo.FK_ItemVariantTitle_Item') IS NOT NULL
ALTER TABLE ItemVariantTitle
DROP CONSTRAINT FK_ItemVariantTitle_Item
go

IF OBJECT_ID('dbo.FK_ProvenanceVariant_Provenance') IS NOT NULL
ALTER TABLE ProvenanceVariant
DROP CONSTRAINT FK_ProvenanceVariant_Provenance
go

IF OBJECT_ID('dbo.FK_ProvenanceVariant_Qualifier') IS NOT NULL
ALTER TABLE ProvenanceVariant
DROP CONSTRAINT FK_ProvenanceVariant_Qualifier
go

IF OBJECT_ID('dbo.FK_RelatedItem_Item') IS NOT NULL
ALTER TABLE RelatedItem
DROP CONSTRAINT FK_RelatedItem_Item
go

IF OBJECT_ID('dbo.Item_RelatedItem_FK1') IS NOT NULL
ALTER TABLE RelatedItem
DROP CONSTRAINT Item_RelatedItem_FK1
go

IF OBJECT_ID('dbo.FK_Series_Provenance') IS NOT NULL
ALTER TABLE Series
DROP CONSTRAINT FK_Series_Provenance
go

IF OBJECT_ID('dbo.FK_SeriesAcquisition_Series') IS NOT NULL
ALTER TABLE SeriesAcquisition
DROP CONSTRAINT FK_SeriesAcquisition_Series
go

IF OBJECT_ID('dbo.FK_SeriesAcquisitionItem_Item') IS NOT NULL
ALTER TABLE SeriesAcquisitionItem
DROP CONSTRAINT FK_SeriesAcquisitionItem_Item
go

IF OBJECT_ID('dbo.SeriesAcquisition_SeriesAcquisitionItem_FK1') IS NOT NULL
ALTER TABLE SeriesAcquisitionItem
DROP CONSTRAINT SeriesAcquisition_SeriesAcquisitionItem_FK1
go

IF OBJECT_ID('dbo.Series_SeriesKeyword_FK1') IS NOT NULL
ALTER TABLE SeriesKeyword
DROP CONSTRAINT Series_SeriesKeyword_FK1
go

IF OBJECT_ID('dbo.Series_SeriesLocation_FK1') IS NOT NULL
ALTER TABLE SeriesLocation
DROP CONSTRAINT Series_SeriesLocation_FK1
go

IF OBJECT_ID('dbo.FK_SeriesSubject_Qualifier') IS NOT NULL
ALTER TABLE Subject
DROP CONSTRAINT FK_SeriesSubject_Qualifier
go

IF OBJECT_ID('dbo.FK_SeriesSubject_Series') IS NOT NULL
ALTER TABLE Subject
DROP CONSTRAINT FK_SeriesSubject_Series
go

IF OBJECT_ID('dbo.FK_Subject_Item') IS NOT NULL
ALTER TABLE Subject
DROP CONSTRAINT FK_Subject_Item
go

/* 
 * TABLE: Component 
 */

CREATE TABLE Component(
    ComponentId          int            IDENTITY(1,1),
    ComponentSequence    int            NULL,
    ItemId               int            NULL,
    DigitalFormatId      int            NULL,
    HardwareId           int            NULL,
    OperatingSystemId    int            NULL,
    SoftwareId           int            NULL,
    CreatedDate          datetime       NOT NULL,
    CreatedBy            varchar(50)    NOT NULL,
    ModifiedDate         datetime       NULL,
    ModifiedBy           varchar(50)    NULL,
    CONSTRAINT Component_PK PRIMARY KEY CLUSTERED (ComponentId)
)
go



IF OBJECT_ID('Component') IS NOT NULL
    PRINT '<<< CREATED TABLE Component >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE Component >>>'
go

/* 
 * TABLE: DigestAlgorithm 
 */

CREATE TABLE DigestAlgorithm(
    DigestAlgorithmId      int            IDENTITY(1,1),
    DigestAlgorithmName    varchar(50)    NOT NULL,
    CreatedDate            datetime       NOT NULL,
    CreatedBy              varchar(50)    NOT NULL,
    ModifiedDate           datetime       NULL,
    ModifiedBy             varchar(50)    NULL,
    CONSTRAINT DigestAlgorithm_PK PRIMARY KEY CLUSTERED (DigestAlgorithmId)
)
go



IF OBJECT_ID('DigestAlgorithm') IS NOT NULL
    PRINT '<<< CREATED TABLE DigestAlgorithm >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE DigestAlgorithm >>>'
go

/* 
 * TABLE: DigitalFormat 
 */

CREATE TABLE DigitalFormat(
    DigitalFormatId                          int            IDENTITY(1,1),
    DigitalFormatFileType                    varchar(5)     NOT NULL,
    DigitalFormatRenderingSoftwareName       varchar(50)    NULL,
    DigitalFormatRenderingSoftwareVersion    varchar(20)    NULL,
    DigitalFormatRegistryId                  int            NULL,
    DigitalFormatMIMEType                    varchar(50)    NOT NULL,
    CreatedDate                              datetime       NOT NULL,
    CreatedBy                                varchar(50)    NOT NULL,
    ModifiedDate                             datetime       NULL,
    ModifiedBy                               varchar(50)    NULL,
    CONSTRAINT DigitalFormat_PK PRIMARY KEY CLUSTERED (DigitalFormatId)
)
go



IF OBJECT_ID('DigitalFormat') IS NOT NULL
    PRINT '<<< CREATED TABLE DigitalFormat >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE DigitalFormat >>>'
go

/* 
 * TABLE: DigitalFormatRegistry 
 */

CREATE TABLE DigitalFormatRegistry(
    DigitalFormatRegistryId        int            IDENTITY(1,1),
    DigitalFormatRegistryName      varchar(50)    NOT NULL,
    DigitalFormatRegistryRoleId    int            NULL,
    CreatedDate                    datetime       NOT NULL,
    CreatedBy                      varchar(50)    NOT NULL,
    ModifiedDate                   datetime       NULL,
    ModifiedBy                     varchar(50)    NULL,
    CONSTRAINT DigitalFormatRegistry_PK PRIMARY KEY CLUSTERED (DigitalFormatRegistryId)
)
go



IF OBJECT_ID('DigitalFormatRegistry') IS NOT NULL
    PRINT '<<< CREATED TABLE DigitalFormatRegistry >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE DigitalFormatRegistry >>>'
go

/* 
 * TABLE: DigitalFormatRegistryRole 
 */

CREATE TABLE DigitalFormatRegistryRole(
    DigitalFormatRegistryRoleId      int            IDENTITY(1,1),
    DigitalFormatRegistryRoleName    varchar(50)    NOT NULL,
    CreatedDate                      datetime       NOT NULL,
    CreatedBy                        varchar(50)    NOT NULL,
    ModifiedDate                     datetime       NULL,
    ModifiedBy                       varchar(50)    NULL,
    CONSTRAINT DigitalFormatRegistryRole_PK PRIMARY KEY CLUSTERED (DigitalFormatRegistryRoleId)
)
go



IF OBJECT_ID('DigitalFormatRegistryRole') IS NOT NULL
    PRINT '<<< CREATED TABLE DigitalFormatRegistryRole >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE DigitalFormatRegistryRole >>>'
go

/* 
 * TABLE: DigitalSignatureKey 
 */

CREATE TABLE DigitalSignatureKey(
    DigitalSignatureKeyId              int             IDENTITY(1,1),
    DigitalSignatureKeyType            varchar(50)     NOT NULL,
    DigitalSignatureKeyValue           varchar(100)    NULL,
    DigitalSignatureKeyInformation     varchar(max)    NULL,
    DigitalSignatureKeyVerification    varchar(max)    NULL,
    CreatedDate                        datetime        NOT NULL,
    CreatedBy                          varchar(50)     NOT NULL,
    ModifiedDate                       datetime        NULL,
    ModifiedBy                         varchar(50)     NULL,
    CONSTRAINT DigitalSignatureKey_PK PRIMARY KEY CLUSTERED (DigitalSignatureKeyId)
)
go



IF OBJECT_ID('DigitalSignatureKey') IS NOT NULL
    PRINT '<<< CREATED TABLE DigitalSignatureKey >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE DigitalSignatureKey >>>'
go

/* 
 * TABLE: FilingUnit 
 */

CREATE TABLE FilingUnit(
    FilingUnitId      int             IDENTITY(1,1),
    FilingUnitName    varchar(255)    NOT NULL,
    CreatedDate       datetime        NOT NULL,
    CreatedBy         varchar(50)     NOT NULL,
    ModifiedDate      datetime        NULL,
    ModifiedBy        varchar(50)     NULL,
    CONSTRAINT FilingUnit_PK PRIMARY KEY CLUSTERED (FilingUnitId)
)
go



IF OBJECT_ID('FilingUnit') IS NOT NULL
    PRINT '<<< CREATED TABLE FilingUnit >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE FilingUnit >>>'
go

/* 
 * TABLE: Hardware 
 */

CREATE TABLE Hardware(
    HardwareId               int             IDENTITY(1,1),
    HardwareName             varchar(50)     NOT NULL,
    HardwareType             char(10)        NULL,
    HardwareLocation         varchar(50)     NULL,
    HardwareDocumentation    varchar(100)    NULL,
    CreatedDate              datetime        NOT NULL,
    CreatedBy                varchar(50)     NOT NULL,
    ModifiedDate             datetime        NULL,
    ModifiedBy               varchar(50)     NULL,
    CONSTRAINT Hardware_PK PRIMARY KEY CLUSTERED (HardwareId)
)
go



IF OBJECT_ID('Hardware') IS NOT NULL
    PRINT '<<< CREATED TABLE Hardware >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE Hardware >>>'
go

/* 
 * TABLE: Item 
 */

CREATE TABLE Item(
    ItemId                         int              NOT NULL,
    SeriesId                       int              NOT NULL,
    ItemTitle                      varchar(250)     NOT NULL,
    ItemDisplayTitle               varchar(250)     NULL,
    ItemDescription                varchar(max)     NULL,
    ItemExtentDescription          varchar(max)     NULL,
    ItemOpenDate                   datetime         NULL,
    ItemSample                     varchar(max)     NULL,
    ItemReceivedFileName           varchar(250)     NULL,
    ItemOriginatorIdentifier       varchar(50)      NULL,
    ItemLanguage                   char(3)          NULL,
    ItemAccessFacilitators         varchar(1250)    NULL,
    ItemExceptions                 varchar(1250)    NULL,
    HardwareId                     int              NULL,
    OperatingSystemId              int              NULL,
    SoftwareId                     int              NULL,
    DigestAlgorithmId              int              NULL,
    ItemReceivedHashOriginator     varchar(50)      NULL,
    ItemReceivedHashDate           datetime         NULL,
    ItemReceivedHashValue          varchar(256)     NULL,
    ItemTechnicalInfrastructure    varchar(1250)    NULL,
    ItemGeneralNote                varchar(max)     NULL,
    DigitalFormatId                int              NULL,
    FilingUnitId                   int              NULL,
    SuperPackageId                 varchar(13)      NULL,
    CreatedDate                    datetime         NOT NULL,
    CreatedBy                      varchar(50)      NOT NULL,
    ModifiedDate                   datetime         NULL,
    ModifiedBy                     varchar(50)      NULL,
    ItemGeoLocationId              int              NULL,
    CONSTRAINT PK_Item PRIMARY KEY CLUSTERED (ItemId)
)
go



IF OBJECT_ID('Item') IS NOT NULL
    PRINT '<<< CREATED TABLE Item >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE Item >>>'
go

/* 
 * TABLE: ItemDate 
 */

CREATE TABLE ItemDate(
    ItemId           int            NOT NULL,
    QualifierId      int            NOT NULL,
    ItemDateValue    varchar(35)    NOT NULL,
    CreatedDate      datetime       NOT NULL,
    CreatedBy        varchar(50)    NOT NULL,
    ModifiedDate     datetime       NULL,
    ModifiedBy       varchar(50)    NULL,
    CONSTRAINT PK_ItemDate PRIMARY KEY CLUSTERED (ItemDateValue)
)
go



IF OBJECT_ID('ItemDate') IS NOT NULL
    PRINT '<<< CREATED TABLE ItemDate >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE ItemDate >>>'
go

/* 
 * TABLE: ItemKeyword 
 */

CREATE TABLE ItemKeyword(
    ItemId          int             NOT NULL,
    Keyword         varchar(250)    NOT NULL,
    CreatedDate     datetime        NOT NULL,
    CreatedBy       varchar(50)     NOT NULL,
    ModifiedDate    datetime        NULL,
    ModifiedBy      varchar(50)     NULL,
    CONSTRAINT PK_ItemKeyword PRIMARY KEY CLUSTERED (Keyword)
)
go



IF OBJECT_ID('ItemKeyword') IS NOT NULL
    PRINT '<<< CREATED TABLE ItemKeyword >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE ItemKeyword >>>'
go

/* 
 * TABLE: ItemLocation 
 */

CREATE TABLE ItemLocation(
    ItemLocationId          int             NOT NULL,
    ItemGeoLocationValue    varchar(250)    NOT NULL,
    CreatedDate             datetime        NOT NULL,
    CreatedBy               varchar(50)     NOT NULL,
    ModifiedDate            datetime        NULL,
    ModifiedBy              varchar(50)     NULL,
    CONSTRAINT PK_ItemLocation_1 PRIMARY KEY CLUSTERED (ItemLocationId)
)
go



IF OBJECT_ID('ItemLocation') IS NOT NULL
    PRINT '<<< CREATED TABLE ItemLocation >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE ItemLocation >>>'
go

/* 
 * TABLE: ItemOtherIdentifier 
 */

CREATE TABLE ItemOtherIdentifier(
    ItemId                     int            NOT NULL,
    QualifierId                int            NOT NULL,
    ItemOtherIdentiferValue    varchar(50)    NOT NULL,
    CreatedDate                datetime       NOT NULL,
    CreatedBy                  varchar(50)    NOT NULL,
    ModifiedDate               datetime       NULL,
    ModifiedBy                 varchar(50)    NULL,
    CONSTRAINT PK_ItemOtherIdentifier_1 PRIMARY KEY CLUSTERED (ItemOtherIdentiferValue)
)
go



IF OBJECT_ID('ItemOtherIdentifier') IS NOT NULL
    PRINT '<<< CREATED TABLE ItemOtherIdentifier >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE ItemOtherIdentifier >>>'
go

/* 
 * TABLE: ItemParty 
 */

CREATE TABLE ItemParty(
    ItemId            int             NOT NULL,
    QualifierId       int             NOT NULL,
    ItemPartyValue    varchar(100)    NOT NULL,
    CreatedDate       datetime        NOT NULL,
    CreatedBy         varchar(50)     NOT NULL,
    ModifiedDate      datetime        NULL,
    ModifiedBy        varchar(50)     NULL,
    CONSTRAINT PK_ItemParty_1 PRIMARY KEY CLUSTERED (ItemPartyValue)
)
go



IF OBJECT_ID('ItemParty') IS NOT NULL
    PRINT '<<< CREATED TABLE ItemParty >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE ItemParty >>>'
go

/* 
 * TABLE: ItemSignature 
 */

CREATE TABLE ItemSignature(
    ItemId                              int            NOT NULL,
    ItemSignatureValue                  varchar(50)    NOT NULL,
    ItemSignatureSigner                 char(10)       NULL,
    ItemSignatureInformationEncoding    char(10)       NULL,
    ItemSignatureMethod                 char(10)       NULL,
    ItemSignatureProperties             char(10)       NULL,
    DigitalSignatureKeyId               int            NULL,
    CreatedDate                         datetime       NOT NULL,
    CreatedBy                           varchar(50)    NOT NULL,
    ModifiedDate                        datetime       NULL,
    ModifiedBy                          varchar(50)    NULL,
    CONSTRAINT ItemSignature_PK PRIMARY KEY CLUSTERED (ItemId, ItemSignatureValue)
)
go



IF OBJECT_ID('ItemSignature') IS NOT NULL
    PRINT '<<< CREATED TABLE ItemSignature >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE ItemSignature >>>'
go

/* 
 * TABLE: ItemVariantTitle 
 */

CREATE TABLE ItemVariantTitle(
    ItemId              int             NOT NULL,
    ItemVariantTitle    varchar(250)    NOT NULL,
    CreatedDate         datetime        NOT NULL,
    CreatedBy           varchar(50)     NOT NULL,
    ModifiedDate        datetime        NULL,
    ModifiedBy          varchar(50)     NULL,
    CONSTRAINT PK_ItemVariantTitle PRIMARY KEY CLUSTERED (ItemVariantTitle)
)
go



IF OBJECT_ID('ItemVariantTitle') IS NOT NULL
    PRINT '<<< CREATED TABLE ItemVariantTitle >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE ItemVariantTitle >>>'
go

/* 
 * TABLE: OperatingSystem 
 */

CREATE TABLE OperatingSystem(
    OperatingSystemId               int             IDENTITY(1,1),
    OperatingSystemName             varchar(100)    NOT NULL,
    OperatingSystemVersion          varchar(20)     NULL,
    OperatingSystemLocation         varchar(50)     NULL,
    OperatingSystemDocumentation    varchar(100)    NULL,
    CreatedDate                     datetime        NOT NULL,
    CreatedBy                       varchar(50)     NOT NULL,
    ModifiedDate                    datetime        NULL,
    ModifiedBy                      varchar(50)     NULL,
    CONSTRAINT OperatingSystem_PK PRIMARY KEY CLUSTERED (OperatingSystemId)
)
go



IF OBJECT_ID('OperatingSystem') IS NOT NULL
    PRINT '<<< CREATED TABLE OperatingSystem >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE OperatingSystem >>>'
go

/* 
 * TABLE: Provenance 
 */

CREATE TABLE Provenance(
    ProvenanceId                       int             IDENTITY(1,1),
    ProvenanceName                     varchar(100)    NOT NULL,
    ProvenanceAdministrativeHistory    varchar(max)    NULL,
    CreatedDate                        datetime        NOT NULL,
    CreatedBy                          varchar(50)     NOT NULL,
    ModifiedDate                       datetime        NULL,
    ModifiedBy                         varchar(50)     NULL,
    CONSTRAINT PK_Provenance PRIMARY KEY CLUSTERED (ProvenanceId)
)
go



IF OBJECT_ID('Provenance') IS NOT NULL
    PRINT '<<< CREATED TABLE Provenance >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE Provenance >>>'
go

/* 
 * TABLE: ProvenanceVariant 
 */

CREATE TABLE ProvenanceVariant(
    ProvenanceVariantId             int             IDENTITY(1,1),
    ProvenanceId                    int             NOT NULL,
    ProvenanceVariantQualifierId    int             NOT NULL,
    ProvenanceVariantName           varchar(100)    NOT NULL,
    CreatedDate                     datetime        NOT NULL,
    CreatedBy                       varchar(50)     NOT NULL,
    ModifiedDate                    datetime        NULL,
    ModifiedBy                      varchar(50)     NULL,
    CONSTRAINT PK_ProvenanceVariant_1 PRIMARY KEY CLUSTERED (ProvenanceId, ProvenanceVariantQualifierId, ProvenanceVariantName)
)
go



IF OBJECT_ID('ProvenanceVariant') IS NOT NULL
    PRINT '<<< CREATED TABLE ProvenanceVariant >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE ProvenanceVariant >>>'
go

/* 
 * TABLE: Qualifier 
 */

CREATE TABLE Qualifier(
    QualifierId      int            IDENTITY(1,1),
    QualifierType    varchar(50)    NOT NULL,
    QualifierName    varchar(50)    NOT NULL,
    CreatedBy        varchar(50)    NOT NULL,
    CreatedDate      datetime       NOT NULL,
    ModifiedBy       varchar(50)    NULL,
    ModifiedDate     datetime       NULL,
    CONSTRAINT PK_Qualifier_1 PRIMARY KEY CLUSTERED (QualifierId)
)
go



IF OBJECT_ID('Qualifier') IS NOT NULL
    PRINT '<<< CREATED TABLE Qualifier >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE Qualifier >>>'
go

/* 
 * TABLE: RelatedItem 
 */

CREATE TABLE RelatedItem(
    ItemId               int            NOT NULL,
    RelatedItemId        int            NOT NULL,
    RelatedItemTypeId    int            NOT NULL,
    CreatedDate          datetime       NOT NULL,
    CreatedBy            varchar(50)    NOT NULL,
    ModifiedDate         datetime       NULL,
    ModifiedBy           varchar(50)    NULL,
    CONSTRAINT PK_RelatedItem PRIMARY KEY CLUSTERED (ItemId, RelatedItemId, RelatedItemTypeId)
)
go



IF OBJECT_ID('RelatedItem') IS NOT NULL
    PRINT '<<< CREATED TABLE RelatedItem >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE RelatedItem >>>'
go

/* 
 * TABLE: RelatedItemType 
 */

CREATE TABLE RelatedItemType(
    RelatedItemTypeId      int            IDENTITY(1,1),
    RelatedItemTypeName    varchar(50)    NOT NULL,
    CreatedDate            datetime       NOT NULL,
    CreatedBy              varchar(50)    NOT NULL,
    ModifiedDate           datetime       NULL,
    ModifiedBy             varchar(50)    NULL,
    CONSTRAINT RelatedItemType_PK PRIMARY KEY CLUSTERED (RelatedItemTypeId)
)
go



IF OBJECT_ID('RelatedItemType') IS NOT NULL
    PRINT '<<< CREATED TABLE RelatedItemType >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE RelatedItemType >>>'
go

/* 
 * TABLE: Series 
 */

CREATE TABLE Series(
    SeriesId                        int              IDENTITY(1,1),
    SeriesTitle                     varchar(250)     NOT NULL,
    SeriesDescription               varchar(max)     NULL,
    SeriesOpenDate                  varchar(50)      NULL,
    SeriesStartYear                 int              NULL,
    SeriesEndYear                   int              NULL,
    SeriesDateRangeDescription      varchar(100)     NULL,
    SeriesExtentDescription         varchar(1024)    NULL,
    SeriesArrangement               varchar(1024)    NULL,
    SeriesRestrictionDescription    varchar(1024)    NULL,
    SeriesGeneralNote               varchar(max)     NULL,
    SeriesIdentifier                varchar(50)      NULL,
    SeriesActivity                  varchar(50)      NULL,
    ProvenanceId                    int              NULL,
    CreatedDate                     datetime         NOT NULL,
    CreatedBy                       varchar(50)      NOT NULL,
    ModifiedDate                    datetime         NULL,
    ModifiedBy                      varchar(50)      NULL,
    CONSTRAINT Series_PK PRIMARY KEY CLUSTERED (SeriesId)
)
go



IF OBJECT_ID('Series') IS NOT NULL
    PRINT '<<< CREATED TABLE Series >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE Series >>>'
go

/* 
 * TABLE: SeriesAcquisition 
 */

CREATE TABLE SeriesAcquisition(
    SeriesAcquisitionId                   int             IDENTITY(1,1),
    SeriesId                              int             NOT NULL,
    SeriesAcquisitionIngestDate           datetime        NULL,
    SeriesAcquisitionReadyToIngest        bit             NOT NULL,
    SeriesAcquisitionIngested             bit             CONSTRAINT [DF_SeriesAcquisition_Ingested] DEFAULT ((0)) NOT NULL,
    SeriesAcquisitionAccessionNumber      varchar(50)     NULL,
    SeriesAcquisitionTransferAuthority    varchar(50)     NULL,
    SeriesAcquisitionSource               varchar(100)    NULL,
    SeriesAcquisitionDirectory            varchar(255)    NULL,
    SeriesAcquisitionReceivedDate         datetime        NULL,
    CreatedDate                           datetime        NOT NULL,
    CreatedBy                             varchar(50)     NOT NULL,
    ModifiedDate                          datetime        NULL,
    ModifiedBy                            varchar(50)     NULL,
    CONSTRAINT SeriesAcquisition_PK PRIMARY KEY CLUSTERED (SeriesAcquisitionId)
)
go



IF OBJECT_ID('SeriesAcquisition') IS NOT NULL
    PRINT '<<< CREATED TABLE SeriesAcquisition >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE SeriesAcquisition >>>'
go

/* 
 * TABLE: SeriesAcquisitionItem 
 */

CREATE TABLE SeriesAcquisitionItem(
    SeriesId               int            NOT NULL,
    ItemId                 int            NOT NULL,
    SeriesAcquisitionId    int            NOT NULL,
    CreatedDate            datetime       NOT NULL,
    CreatedBy              varchar(50)    NOT NULL,
    ModifiedDate           datetime       NULL,
    ModifiedBy             varchar(50)    NULL,
    CONSTRAINT SeriesAcquisitionItem_PK PRIMARY KEY CLUSTERED (SeriesId, ItemId)
)
go



IF OBJECT_ID('SeriesAcquisitionItem') IS NOT NULL
    PRINT '<<< CREATED TABLE SeriesAcquisitionItem >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE SeriesAcquisitionItem >>>'
go

/* 
 * TABLE: SeriesKeyword 
 */

CREATE TABLE SeriesKeyword(
    SeriesId           int            NOT NULL,
    SeriesKeyword      varchar(50)    NOT NULL,
    CreatedDate        datetime       NOT NULL,
    CreatedBy          varchar(50)    NOT NULL,
    ModifiedDate       datetime       NULL,
    ModifiedBy         varchar(50)    NULL,
    SeriesKeywordId    int            IDENTITY(1,1),
    CONSTRAINT SeriesKeyword_PK PRIMARY KEY CLUSTERED (SeriesId, SeriesKeyword)
)
go



IF OBJECT_ID('SeriesKeyword') IS NOT NULL
    PRINT '<<< CREATED TABLE SeriesKeyword >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE SeriesKeyword >>>'
go

/* 
 * TABLE: SeriesLocation 
 */

CREATE TABLE SeriesLocation(
    SeriesId                  int             NOT NULL,
    SeriesGeoLocationValue    varchar(100)    NOT NULL,
    CreatedDate               datetime        NOT NULL,
    CreatedBy                 varchar(50)     NOT NULL,
    ModifiedDate              datetime        NULL,
    ModifiedBy                varchar(50)     NULL,
    SeriesLocationId          int             IDENTITY(1,1),
    CONSTRAINT SeriesLocation_PK PRIMARY KEY CLUSTERED (SeriesId, SeriesGeoLocationValue)
)
go



IF OBJECT_ID('SeriesLocation') IS NOT NULL
    PRINT '<<< CREATED TABLE SeriesLocation >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE SeriesLocation >>>'
go

/* 
 * TABLE: Software 
 */

CREATE TABLE Software(
    SoftwareId                           int              IDENTITY(1,1),
    SoftwareName                         varchar(50)      NOT NULL,
    SoftwareVersion                      varchar(20)      NULL,
    SoftwareDependencyDescription        varchar(1250)    NULL,
    SoftwarePurposeDescription           varchar(1250)    NULL,
    SoftwareCharacteristicDescription    varchar(1250)    NULL,
    SoftwareLocation                     varchar(50)      NULL,
    SoftwareNote                         varchar(max)     NULL,
    CreatedDate                          datetime         NOT NULL,
    CreatedBy                            varchar(50)      NOT NULL,
    ModifiedDate                         datetime         NULL,
    ModifiedBy                           varchar(50)      NULL,
    CONSTRAINT Software_PK PRIMARY KEY CLUSTERED (SoftwareId)
)
go



IF OBJECT_ID('Software') IS NOT NULL
    PRINT '<<< CREATED TABLE Software >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE Software >>>'
go

/* 
 * TABLE: Subject 
 */

CREATE TABLE Subject(
    SeriesId             int             NOT NULL,
    QualifierId          int             NOT NULL,
    SeriesSubjectName    varchar(100)    NOT NULL,
    CreatedDate          datetime        NOT NULL,
    CreatedBy            varchar(50)     NOT NULL,
    ModifiedDate         datetime        NULL,
    ModifiedBy           varchar(50)     NULL,
    SeriesSubjectId      int             IDENTITY(1,1),
    ItemId               int             NULL,
    CONSTRAINT PK_SeriesSubject_1 PRIMARY KEY CLUSTERED (SeriesSubjectName)
)
go



IF OBJECT_ID('Subject') IS NOT NULL
    PRINT '<<< CREATED TABLE Subject >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE Subject >>>'
go

/* 
 * TABLE: sysdiagrams 
 */

CREATE TABLE sysdiagrams(
    name            char(10)          NOT NULL,
    principal_id    int               NOT NULL,
    diagram_id      int               IDENTITY(1,1),
    version         int               NULL,
    definition      varbinary(max)    NULL,
    CONSTRAINT PK__sysdiagrams__68487DD7 PRIMARY KEY CLUSTERED (diagram_id),
    CONSTRAINT UK_principal_name  UNIQUE (principal_id, name)
)
go



IF OBJECT_ID('sysdiagrams') IS NOT NULL
    PRINT '<<< CREATED TABLE sysdiagrams >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE sysdiagrams >>>'
go

/* 
 * INDEX: IX1COMPONENT_DIGITALFORMAT 
 */

CREATE INDEX IX1COMPONENT_DIGITALFORMAT ON Component(DigitalFormatId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('Component') AND name='IX1COMPONENT_DIGITALFORMAT')
    PRINT '<<< CREATED INDEX Component.IX1COMPONENT_DIGITALFORMAT >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX Component.IX1COMPONENT_DIGITALFORMAT >>>'
go

/* 
 * INDEX: IX1COMPONENT_HARDWARE 
 */

CREATE INDEX IX1COMPONENT_HARDWARE ON Component(HardwareId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('Component') AND name='IX1COMPONENT_HARDWARE')
    PRINT '<<< CREATED INDEX Component.IX1COMPONENT_HARDWARE >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX Component.IX1COMPONENT_HARDWARE >>>'
go

/* 
 * INDEX: IX1COMPONENT_OPERATINGSYSTEM 
 */

CREATE INDEX IX1COMPONENT_OPERATINGSYSTEM ON Component(OperatingSystemId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('Component') AND name='IX1COMPONENT_OPERATINGSYSTEM')
    PRINT '<<< CREATED INDEX Component.IX1COMPONENT_OPERATINGSYSTEM >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX Component.IX1COMPONENT_OPERATINGSYSTEM >>>'
go

/* 
 * INDEX: IX1COMPONENT_SOFTWARE 
 */

CREATE INDEX IX1COMPONENT_SOFTWARE ON Component(SoftwareId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('Component') AND name='IX1COMPONENT_SOFTWARE')
    PRINT '<<< CREATED INDEX Component.IX1COMPONENT_SOFTWARE >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX Component.IX1COMPONENT_SOFTWARE >>>'
go

/* 
 * INDEX: IX1DIGITALFORMAT_DIGITALFORMATREGISTRY 
 */

CREATE INDEX IX1DIGITALFORMAT_DIGITALFORMATREGISTRY ON DigitalFormat(DigitalFormatRegistryId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('DigitalFormat') AND name='IX1DIGITALFORMAT_DIGITALFORMATREGISTRY')
    PRINT '<<< CREATED INDEX DigitalFormat.IX1DIGITALFORMAT_DIGITALFORMATREGISTRY >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX DigitalFormat.IX1DIGITALFORMAT_DIGITALFORMATREGISTRY >>>'
go

/* 
 * INDEX: IX1DIGITALFORMATREGISTRY_DIGITALFORMATREGISTRYROLE 
 */

CREATE INDEX IX1DIGITALFORMATREGISTRY_DIGITALFORMATREGISTRYROLE ON DigitalFormatRegistry(DigitalFormatRegistryRoleId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('DigitalFormatRegistry') AND name='IX1DIGITALFORMATREGISTRY_DIGITALFORMATREGISTRYROLE')
    PRINT '<<< CREATED INDEX DigitalFormatRegistry.IX1DIGITALFORMATREGISTRY_DIGITALFORMATREGISTRYROLE >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX DigitalFormatRegistry.IX1DIGITALFORMATREGISTRY_DIGITALFORMATREGISTRYROLE >>>'
go

/* 
 * INDEX: IX1ITEM_DIGESTALGORITHM 
 */

CREATE INDEX IX1ITEM_DIGESTALGORITHM ON Item(DigestAlgorithmId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('Item') AND name='IX1ITEM_DIGESTALGORITHM')
    PRINT '<<< CREATED INDEX Item.IX1ITEM_DIGESTALGORITHM >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX Item.IX1ITEM_DIGESTALGORITHM >>>'
go

/* 
 * INDEX: IX1ITEM_DIGITALFORMAT 
 */

CREATE INDEX IX1ITEM_DIGITALFORMAT ON Item(DigitalFormatId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('Item') AND name='IX1ITEM_DIGITALFORMAT')
    PRINT '<<< CREATED INDEX Item.IX1ITEM_DIGITALFORMAT >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX Item.IX1ITEM_DIGITALFORMAT >>>'
go

/* 
 * INDEX: IX1ITEM_FILINGUNIT 
 */

CREATE INDEX IX1ITEM_FILINGUNIT ON Item(FilingUnitId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('Item') AND name='IX1ITEM_FILINGUNIT')
    PRINT '<<< CREATED INDEX Item.IX1ITEM_FILINGUNIT >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX Item.IX1ITEM_FILINGUNIT >>>'
go

/* 
 * INDEX: IX1ITEM_HARDWARE 
 */

CREATE INDEX IX1ITEM_HARDWARE ON Item(HardwareId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('Item') AND name='IX1ITEM_HARDWARE')
    PRINT '<<< CREATED INDEX Item.IX1ITEM_HARDWARE >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX Item.IX1ITEM_HARDWARE >>>'
go

/* 
 * INDEX: IX1ITEM_OPERATINGSYSTEM 
 */

CREATE INDEX IX1ITEM_OPERATINGSYSTEM ON Item(OperatingSystemId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('Item') AND name='IX1ITEM_OPERATINGSYSTEM')
    PRINT '<<< CREATED INDEX Item.IX1ITEM_OPERATINGSYSTEM >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX Item.IX1ITEM_OPERATINGSYSTEM >>>'
go

/* 
 * INDEX: IX1ITEM_SERIES 
 */

CREATE INDEX IX1ITEM_SERIES ON Item(SeriesId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('Item') AND name='IX1ITEM_SERIES')
    PRINT '<<< CREATED INDEX Item.IX1ITEM_SERIES >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX Item.IX1ITEM_SERIES >>>'
go

/* 
 * INDEX: IX1ITEM_SOFTWARE 
 */

CREATE INDEX IX1ITEM_SOFTWARE ON Item(SoftwareId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('Item') AND name='IX1ITEM_SOFTWARE')
    PRINT '<<< CREATED INDEX Item.IX1ITEM_SOFTWARE >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX Item.IX1ITEM_SOFTWARE >>>'
go

/* 
 * INDEX: IX1ITEM_ITEMLOCATION 
 */

CREATE INDEX IX1ITEM_ITEMLOCATION ON Item(ItemGeoLocationId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('Item') AND name='IX1ITEM_ITEMLOCATION')
    PRINT '<<< CREATED INDEX Item.IX1ITEM_ITEMLOCATION >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX Item.IX1ITEM_ITEMLOCATION >>>'
go

/* 
 * INDEX: IX1ITEMDATE_QUALIFIER 
 */

CREATE INDEX IX1ITEMDATE_QUALIFIER ON ItemDate(QualifierId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('ItemDate') AND name='IX1ITEMDATE_QUALIFIER')
    PRINT '<<< CREATED INDEX ItemDate.IX1ITEMDATE_QUALIFIER >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX ItemDate.IX1ITEMDATE_QUALIFIER >>>'
go

/* 
 * INDEX: IX1ITEMDATE_ITEM 
 */

CREATE INDEX IX1ITEMDATE_ITEM ON ItemDate(ItemId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('ItemDate') AND name='IX1ITEMDATE_ITEM')
    PRINT '<<< CREATED INDEX ItemDate.IX1ITEMDATE_ITEM >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX ItemDate.IX1ITEMDATE_ITEM >>>'
go

/* 
 * INDEX: IX1ITEMKEYWORD_ITEM 
 */

CREATE INDEX IX1ITEMKEYWORD_ITEM ON ItemKeyword(ItemId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('ItemKeyword') AND name='IX1ITEMKEYWORD_ITEM')
    PRINT '<<< CREATED INDEX ItemKeyword.IX1ITEMKEYWORD_ITEM >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX ItemKeyword.IX1ITEMKEYWORD_ITEM >>>'
go

/* 
 * INDEX: IX1ITEMOTHERIDENTIFIER_ITEM 
 */

CREATE INDEX IX1ITEMOTHERIDENTIFIER_ITEM ON ItemOtherIdentifier(ItemId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('ItemOtherIdentifier') AND name='IX1ITEMOTHERIDENTIFIER_ITEM')
    PRINT '<<< CREATED INDEX ItemOtherIdentifier.IX1ITEMOTHERIDENTIFIER_ITEM >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX ItemOtherIdentifier.IX1ITEMOTHERIDENTIFIER_ITEM >>>'
go

/* 
 * INDEX: IX1ITEMOTHERIDENTIFIER_QUALIFIER 
 */

CREATE INDEX IX1ITEMOTHERIDENTIFIER_QUALIFIER ON ItemOtherIdentifier(QualifierId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('ItemOtherIdentifier') AND name='IX1ITEMOTHERIDENTIFIER_QUALIFIER')
    PRINT '<<< CREATED INDEX ItemOtherIdentifier.IX1ITEMOTHERIDENTIFIER_QUALIFIER >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX ItemOtherIdentifier.IX1ITEMOTHERIDENTIFIER_QUALIFIER >>>'
go

/* 
 * INDEX: IX1ITEMPARTY_QUALIFIER 
 */

CREATE INDEX IX1ITEMPARTY_QUALIFIER ON ItemParty(QualifierId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('ItemParty') AND name='IX1ITEMPARTY_QUALIFIER')
    PRINT '<<< CREATED INDEX ItemParty.IX1ITEMPARTY_QUALIFIER >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX ItemParty.IX1ITEMPARTY_QUALIFIER >>>'
go

/* 
 * INDEX: IX1ITEMSIGNATURE_DIGITALSIGNATUREKEY 
 */

CREATE INDEX IX1ITEMSIGNATURE_DIGITALSIGNATUREKEY ON ItemSignature(DigitalSignatureKeyId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('ItemSignature') AND name='IX1ITEMSIGNATURE_DIGITALSIGNATUREKEY')
    PRINT '<<< CREATED INDEX ItemSignature.IX1ITEMSIGNATURE_DIGITALSIGNATUREKEY >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX ItemSignature.IX1ITEMSIGNATURE_DIGITALSIGNATUREKEY >>>'
go

/* 
 * INDEX: IX1ITEMVARIANTTITLE_ITEM 
 */

CREATE INDEX IX1ITEMVARIANTTITLE_ITEM ON ItemVariantTitle(ItemId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('ItemVariantTitle') AND name='IX1ITEMVARIANTTITLE_ITEM')
    PRINT '<<< CREATED INDEX ItemVariantTitle.IX1ITEMVARIANTTITLE_ITEM >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX ItemVariantTitle.IX1ITEMVARIANTTITLE_ITEM >>>'
go

/* 
 * INDEX: IX1RELATEDITEM_RELATEDITEMTYPE 
 */

CREATE INDEX IX1RELATEDITEM_RELATEDITEMTYPE ON RelatedItem(RelatedItemTypeId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('RelatedItem') AND name='IX1RELATEDITEM_RELATEDITEMTYPE')
    PRINT '<<< CREATED INDEX RelatedItem.IX1RELATEDITEM_RELATEDITEMTYPE >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX RelatedItem.IX1RELATEDITEM_RELATEDITEMTYPE >>>'
go

/* 
 * INDEX: IX1SERIES_PROVENANCE 
 */

CREATE INDEX IX1SERIES_PROVENANCE ON Series(ProvenanceId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('Series') AND name='IX1SERIES_PROVENANCE')
    PRINT '<<< CREATED INDEX Series.IX1SERIES_PROVENANCE >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX Series.IX1SERIES_PROVENANCE >>>'
go

/* 
 * INDEX: IX1SERIESACQUISITION_SERIES 
 */

CREATE INDEX IX1SERIESACQUISITION_SERIES ON SeriesAcquisition(SeriesId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('SeriesAcquisition') AND name='IX1SERIESACQUISITION_SERIES')
    PRINT '<<< CREATED INDEX SeriesAcquisition.IX1SERIESACQUISITION_SERIES >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX SeriesAcquisition.IX1SERIESACQUISITION_SERIES >>>'
go

/* 
 * INDEX: IX1SERIESACQUISITIONITEM_SERIESACQUISITION 
 */

CREATE INDEX IX1SERIESACQUISITIONITEM_SERIESACQUISITION ON SeriesAcquisitionItem(SeriesAcquisitionId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('SeriesAcquisitionItem') AND name='IX1SERIESACQUISITIONITEM_SERIESACQUISITION')
    PRINT '<<< CREATED INDEX SeriesAcquisitionItem.IX1SERIESACQUISITIONITEM_SERIESACQUISITION >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX SeriesAcquisitionItem.IX1SERIESACQUISITIONITEM_SERIESACQUISITION >>>'
go

/* 
 * INDEX: IX1SERIESSUBJECT_QUALIFIER 
 */

CREATE INDEX IX1SERIESSUBJECT_QUALIFIER ON Subject(QualifierId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('Subject') AND name='IX1SERIESSUBJECT_QUALIFIER')
    PRINT '<<< CREATED INDEX Subject.IX1SERIESSUBJECT_QUALIFIER >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX Subject.IX1SERIESSUBJECT_QUALIFIER >>>'
go

/* 
 * INDEX: IX1SERIESSUBJECT_SERIES 
 */

CREATE INDEX IX1SERIESSUBJECT_SERIES ON Subject(SeriesId)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('Subject') AND name='IX1SERIESSUBJECT_SERIES')
    PRINT '<<< CREATED INDEX Subject.IX1SERIESSUBJECT_SERIES >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX Subject.IX1SERIESSUBJECT_SERIES >>>'
go

/* 
 * TABLE: Component 
 */

ALTER TABLE Component ADD CONSTRAINT DigitalFormat_Component_FK1 
    FOREIGN KEY (DigitalFormatId)
    REFERENCES DigitalFormat(DigitalFormatId)
go

ALTER TABLE Component ADD CONSTRAINT Hardware_Component_FK1 
    FOREIGN KEY (HardwareId)
    REFERENCES Hardware(HardwareId)
go

ALTER TABLE Component ADD CONSTRAINT OperatingSystem_Component_FK1 
    FOREIGN KEY (OperatingSystemId)
    REFERENCES OperatingSystem(OperatingSystemId)
go

ALTER TABLE Component ADD CONSTRAINT Software_Component_FK1 
    FOREIGN KEY (SoftwareId)
    REFERENCES Software(SoftwareId)
go


/* 
 * TABLE: DigitalFormat 
 */

ALTER TABLE DigitalFormat ADD CONSTRAINT DigitalFormatRegistry_DigitalFormat_FK1 
    FOREIGN KEY (DigitalFormatRegistryId)
    REFERENCES DigitalFormatRegistry(DigitalFormatRegistryId)
go


/* 
 * TABLE: DigitalFormatRegistry 
 */

ALTER TABLE DigitalFormatRegistry ADD CONSTRAINT DigitalFormatRegistryRole_DigitalFormatRegistry_FK1 
    FOREIGN KEY (DigitalFormatRegistryRoleId)
    REFERENCES DigitalFormatRegistryRole(DigitalFormatRegistryRoleId)
go


/* 
 * TABLE: Item 
 */

ALTER TABLE Item ADD CONSTRAINT DigestAlgorithm_Item_FK1 
    FOREIGN KEY (DigestAlgorithmId)
    REFERENCES DigestAlgorithm(DigestAlgorithmId)
go

ALTER TABLE Item ADD CONSTRAINT DigitalFormat_Item_FK1 
    FOREIGN KEY (DigitalFormatId)
    REFERENCES DigitalFormat(DigitalFormatId)
go

ALTER TABLE Item ADD CONSTRAINT FilingUnit_Item_FK1 
    FOREIGN KEY (FilingUnitId)
    REFERENCES FilingUnit(FilingUnitId)
go

ALTER TABLE Item ADD CONSTRAINT FK_Item_ItemLocation 
    FOREIGN KEY (ItemGeoLocationId)
    REFERENCES ItemLocation(ItemLocationId)
go

ALTER TABLE Item ADD CONSTRAINT Hardware_Item_FK1 
    FOREIGN KEY (HardwareId)
    REFERENCES Hardware(HardwareId)
go

ALTER TABLE Item ADD CONSTRAINT OperatingSystem_Item_FK1 
    FOREIGN KEY (OperatingSystemId)
    REFERENCES OperatingSystem(OperatingSystemId)
go

ALTER TABLE Item ADD CONSTRAINT Series_Item_FK1 
    FOREIGN KEY (SeriesId)
    REFERENCES Series(SeriesId)
go

ALTER TABLE Item ADD CONSTRAINT Software_Item_FK1 
    FOREIGN KEY (SoftwareId)
    REFERENCES Software(SoftwareId)
go


/* 
 * TABLE: ItemDate 
 */

ALTER TABLE ItemDate ADD CONSTRAINT FK_ItemDate_Item 
    FOREIGN KEY (ItemId)
    REFERENCES Item(ItemId)
go

ALTER TABLE ItemDate ADD CONSTRAINT FK_ItemDate_Qualifier 
    FOREIGN KEY (QualifierId)
    REFERENCES Qualifier(QualifierId)
go


/* 
 * TABLE: ItemKeyword 
 */

ALTER TABLE ItemKeyword ADD CONSTRAINT FK_ItemKeyword_Item 
    FOREIGN KEY (ItemId)
    REFERENCES Item(ItemId)
go


/* 
 * TABLE: ItemOtherIdentifier 
 */

ALTER TABLE ItemOtherIdentifier ADD CONSTRAINT FK_ItemOtherIdentifier_Item 
    FOREIGN KEY (ItemId)
    REFERENCES Item(ItemId)
go

ALTER TABLE ItemOtherIdentifier ADD CONSTRAINT FK_ItemOtherIdentifier_Qualifier 
    FOREIGN KEY (QualifierId)
    REFERENCES Qualifier(QualifierId)
go


/* 
 * TABLE: ItemParty 
 */

ALTER TABLE ItemParty ADD CONSTRAINT FK_ItemParty_Item 
    FOREIGN KEY (ItemId)
    REFERENCES Item(ItemId)
go

ALTER TABLE ItemParty ADD CONSTRAINT FK_ItemParty_Qualifier 
    FOREIGN KEY (QualifierId)
    REFERENCES Qualifier(QualifierId)
go


/* 
 * TABLE: ItemSignature 
 */

ALTER TABLE ItemSignature ADD CONSTRAINT DigitalSignatureKey_ItemSignature_FK1 
    FOREIGN KEY (DigitalSignatureKeyId)
    REFERENCES DigitalSignatureKey(DigitalSignatureKeyId)
go

ALTER TABLE ItemSignature ADD CONSTRAINT FK_ItemSignature_Item 
    FOREIGN KEY (ItemId)
    REFERENCES Item(ItemId)
go


/* 
 * TABLE: ItemVariantTitle 
 */

ALTER TABLE ItemVariantTitle ADD CONSTRAINT FK_ItemVariantTitle_Item 
    FOREIGN KEY (ItemId)
    REFERENCES Item(ItemId)
go


/* 
 * TABLE: ProvenanceVariant 
 */

ALTER TABLE ProvenanceVariant ADD CONSTRAINT FK_ProvenanceVariant_Provenance 
    FOREIGN KEY (ProvenanceId)
    REFERENCES Provenance(ProvenanceId)
go

ALTER TABLE ProvenanceVariant ADD CONSTRAINT FK_ProvenanceVariant_Qualifier 
    FOREIGN KEY (ProvenanceVariantQualifierId)
    REFERENCES Qualifier(QualifierId)
go


/* 
 * TABLE: RelatedItem 
 */

ALTER TABLE RelatedItem ADD CONSTRAINT FK_RelatedItem_Item 
    FOREIGN KEY (ItemId)
    REFERENCES Item(ItemId)
go

ALTER TABLE RelatedItem ADD CONSTRAINT Item_RelatedItem_FK1 
    FOREIGN KEY (RelatedItemTypeId)
    REFERENCES RelatedItemType(RelatedItemTypeId)
go


/* 
 * TABLE: Series 
 */

ALTER TABLE Series ADD CONSTRAINT FK_Series_Provenance 
    FOREIGN KEY (ProvenanceId)
    REFERENCES Provenance(ProvenanceId)
go


/* 
 * TABLE: SeriesAcquisition 
 */

ALTER TABLE SeriesAcquisition ADD CONSTRAINT FK_SeriesAcquisition_Series 
    FOREIGN KEY (SeriesId)
    REFERENCES Series(SeriesId)
go


/* 
 * TABLE: SeriesAcquisitionItem 
 */

ALTER TABLE SeriesAcquisitionItem ADD CONSTRAINT FK_SeriesAcquisitionItem_Item 
    FOREIGN KEY (ItemId)
    REFERENCES Item(ItemId)
go

ALTER TABLE SeriesAcquisitionItem ADD CONSTRAINT SeriesAcquisition_SeriesAcquisitionItem_FK1 
    FOREIGN KEY (SeriesAcquisitionId)
    REFERENCES SeriesAcquisition(SeriesAcquisitionId)
go


/* 
 * TABLE: SeriesKeyword 
 */

ALTER TABLE SeriesKeyword ADD CONSTRAINT Series_SeriesKeyword_FK1 
    FOREIGN KEY (SeriesId)
    REFERENCES Series(SeriesId)
go


/* 
 * TABLE: SeriesLocation 
 */

ALTER TABLE SeriesLocation ADD CONSTRAINT Series_SeriesLocation_FK1 
    FOREIGN KEY (SeriesId)
    REFERENCES Series(SeriesId)
go


/* 
 * TABLE: Subject 
 */

ALTER TABLE Subject ADD CONSTRAINT FK_SeriesSubject_Qualifier 
    FOREIGN KEY (QualifierId)
    REFERENCES Qualifier(QualifierId)
go

ALTER TABLE Subject ADD CONSTRAINT FK_SeriesSubject_Series 
    FOREIGN KEY (SeriesId)
    REFERENCES Series(SeriesId)
go

ALTER TABLE Subject ADD CONSTRAINT FK_Subject_Item 
    FOREIGN KEY (ItemId)
    REFERENCES Item(ItemId)
go


/* 
 * FUNCTION: fn_diagramobjects 
 */


	CREATE FUNCTION fn_diagramobjects() 
	RETURNS int
	WITH EXECUTE AS N'dbo'
	AS
	BEGIN
		declare @id_upgraddiagrams		int
		declare @id_sysdiagrams			int
		declare @id_helpdiagrams		int
		declare @id_helpdiagramdefinition	int
		declare @id_creatediagram	int
		declare @id_renamediagram	int
		declare @id_alterdiagram 	int 
		declare @id_dropdiagram		int
		declare @InstalledObjects	int

		select @InstalledObjects = 0

		select 	@id_upgraddiagrams = object_id(N'dbo.sp_upgraddiagrams'),
			@id_sysdiagrams = object_id(N'dbo.sysdiagrams'),
			@id_helpdiagrams = object_id(N'dbo.sp_helpdiagrams'),
			@id_helpdiagramdefinition = object_id(N'dbo.sp_helpdiagramdefinition'),
			@id_creatediagram = object_id(N'dbo.sp_creatediagram'),
			@id_renamediagram = object_id(N'dbo.sp_renamediagram'),
			@id_alterdiagram = object_id(N'dbo.sp_alterdiagram'), 
			@id_dropdiagram = object_id(N'dbo.sp_dropdiagram')

		if @id_upgraddiagrams is not null
			select @InstalledObjects = @InstalledObjects + 1
		if @id_sysdiagrams is not null
			select @InstalledObjects = @InstalledObjects + 2
		if @id_helpdiagrams is not null
			select @InstalledObjects = @InstalledObjects + 4
		if @id_helpdiagramdefinition is not null
			select @InstalledObjects = @InstalledObjects + 8
		if @id_creatediagram is not null
			select @InstalledObjects = @InstalledObjects + 16
		if @id_renamediagram is not null
			select @InstalledObjects = @InstalledObjects + 32
		if @id_alterdiagram  is not null
			select @InstalledObjects = @InstalledObjects + 64
		if @id_dropdiagram is not null
			select @InstalledObjects = @InstalledObjects + 128
		
		return @InstalledObjects 
	END
go
IF OBJECT_ID('fn_diagramobjects') IS NOT NULL
    PRINT '<<< CREATED FUNCTION fn_diagramobjects >>>'
ELSE
    PRINT '<<< FAILED CREATING FUNCTION fn_diagramobjects >>>'
go


/* 
 * PROCEDURE: DEMO_CreateDigitalFormat 
 */

CREATE PROCEDURE [DEMO_CreateDigitalFormat]
	@DigitalFormatFileType VARCHAR (3),
	@DigitalFormatMIMEType VARCHAR (50)

AS
BEGIN
	INSERT INTO [PeDALS_DLB].[dbo].[DigitalFormat]
           ([DigitalFormatFileType]
           ,[DigitalFormatMimeType]
           ,[CreatedDate]
           ,[CreatedBy])
     VALUES
           (@DigitalFormatFileType
           ,@DigitalFormatMimeType
           ,GETDATE()
           ,'Arizona State Library Archivist')

	RETURN @@IDENTITY

END
go
IF OBJECT_ID('DEMO_CreateDigitalFormat') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DEMO_CreateDigitalFormat >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DEMO_CreateDigitalFormat >>>'
go


/* 
 * PROCEDURE: DEMO_CreateProvenance 
 */

CREATE PROCEDURE [DEMO_CreateProvenance]
	(@ProvenanceName VARCHAR (100),
	@ProvenanceAdministrativeHistory VARCHAR (MAX))
AS
	BEGIN
		SET NOCOUNT ON;

		INSERT INTO [PeDALS_DLB].[dbo].[Provenance]
           ([ProvenanceName]
			,[ProvenanceAdministrativeHistory]
			,[CreatedDate]
			,[CreatedBy])
		VALUES
           (@ProvenanceName
			,@ProvenanceAdministrativeHistory
			,GetDate()
			,'Arizona State Library Archivist')

	RETURN @@IDENTITY
		
	END
go
IF OBJECT_ID('DEMO_CreateProvenance') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DEMO_CreateProvenance >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DEMO_CreateProvenance >>>'
go


/* 
 * PROCEDURE: DEMO_CreateSeries 
 */

CREATE PROCEDURE [DEMO_CreateSeries]
	@SeriesTitle VARCHAR (250),
	@SeriesDescription VARCHAR (MAX),
	@SeriesOpenDate DATETIME,
	@SeriesStartYear INT,
	@SeriesDateRangeDescription VARCHAR (100),
	@SeriesExtentDescription VARCHAR (1024),	
	@SeriesArrangement VARCHAR (1024),
	@SeriesRestrictionDescription VARCHAR (1024),
	@SeriesGeneralNote VARCHAR (MAX),
	@SeriesIdentifier VARCHAR (50),
	@SeriesActivity VARCHAR (50),
	@ProvenanceId INT

AS
	BEGIN
		INSERT INTO [PeDALS_DLB].[dbo].[Series]
           ([SeriesTitle]
           ,[SeriesDescription]
           ,[SeriesOpenDate]
           ,[SeriesStartYear]
           ,[SeriesDateRangeDescription]
           ,[SeriesExtentDescription]
           ,[SeriesArrangement]
           ,[SeriesRestrictionDescription]
           ,[SeriesGeneralNote]
           ,[SeriesIdentifier]
           ,[SeriesActivity]
           ,[ProvenanceId]
           ,[CreatedDate]
           ,[CreatedBy])
     VALUES
           (@SeriesTitle
           ,@SeriesDescription
           ,@SeriesOpenDate
           ,@SeriesStartYear
           ,@SeriesDateRangeDescription
           ,@SeriesExtentDescription
           ,@SeriesArrangement
           ,@SeriesRestrictionDescription
           ,@SeriesGeneralNote
           ,@SeriesIdentifier
           ,@SeriesActivity
           ,@ProvenanceId
           ,GETDATE()
           ,'Arizona State Library Archivist')

	RETURN @@IDENTITY
	END
go
IF OBJECT_ID('DEMO_CreateSeries') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DEMO_CreateSeries >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DEMO_CreateSeries >>>'
go


/* 
 * PROCEDURE: DEMO_CreateSeriesAquisition 
 */

CREATE PROCEDURE [DEMO_CreateSeriesAquisition]
	@SeriesAcquisitionAccesionNumber VARCHAR (50),
	@SeriesAcquisitionSource VARCHAR (100),
	@SeriesAcquisitionDirectory VARCHAR (255)
AS
BEGIN
	INSERT INTO [PeDALS_DLB].[dbo].[SeriesAcquisition]
           ([SeriesId]
           ,[SeriesAcquisitionIngestDate]
           ,[SeriesAcquisitionReadyToIngest]
           ,[SeriesAcquisitionIngested]
           ,[SeriesAcquisitionAccessionNumber]
           ,[SeriesAcquisitionSource]
           ,[SeriesAcquisitionDirectory]
           ,[SeriesAcquisitionReceivedDate]
           ,[CreatedDate]
           ,[CreatedBy])
     VALUES
           (1,
			GetDATE(),
			1,
			0,		
			@SeriesAcquisitionAccesionNumber,
            @SeriesAcquisitionSource
           ,@SeriesAcquisitionDirectory
           ,GetDate()
           ,GetDate()
           ,'Arizona State Library Archivist')

	RETURN @@IDENTITY

END
go
IF OBJECT_ID('DEMO_CreateSeriesAquisition') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DEMO_CreateSeriesAquisition >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DEMO_CreateSeriesAquisition >>>'
go


/* 
 * PROCEDURE: DEMO_CreateSoftware 
 */

CREATE PROCEDURE [DEMO_CreateSoftware]
	@SoftwareName VARCHAR (50),
	@SoftwareVersion VARCHAR (20)
	

AS
BEGIN
	INSERT INTO [PeDALS_DLB].[dbo].[Software](
			SoftwareName,
			SoftwareVersion,
			CreatedDate,
			CreatedBy)
     VALUES
           (@SoftwareName
           ,@SoftwareVersion
           ,GETDATE()
           ,'Arizona State Library Archivist')

	RETURN @@IDENTITY

END
go
IF OBJECT_ID('DEMO_CreateSoftware') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DEMO_CreateSoftware >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DEMO_CreateSoftware >>>'
go


/* 
 * PROCEDURE: InsertException 
 */

CREATE Procedure [InsertException]
	@ProvenanceId INT,
	@SeriesId INT,
	@SeriesAcquisitionId INT,
	@Desc INT
AS
INSERT INTO [PeDALS_DLB].[dbo].[Exceptions]
           ([ProvenanceId]
           ,[SeriesId]
           ,[SeriesAcquisitionId]
           ,[Description]
           ,[CreatedDate]
           ,[CreatedBy])
     VALUES
           (
			@ProvenanceId,
			@SeriesId,
			@SeriesAcquisitionId,
			@Desc,
			GETDATE(),
			'BizTalk'
           )
go
IF OBJECT_ID('InsertException') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE InsertException >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE InsertException >>>'
go


/* 
 * PROCEDURE: PAC_CreateProvenance 
 */

CREATE PROCEDURE [PAC_CreateProvenance]
	(@ProvenanceName VARCHAR (100),
	@ProvenanceAdministrativeHistory VARCHAR (MAX),
	@ProvenanceId INT OUTPUT)
AS
BEGIN
	TRY
		SET NOCOUNT ON
		BEGIN TRANSACTION
		INSERT INTO [PeDALS_DLB].[dbo].[Provenance]
           ([ProvenanceName]
			,[ProvenanceAdministrativeHistory]
			,[CreatedDate]
			,[CreatedBy])
		VALUES
           (@ProvenanceName
			,@ProvenanceAdministrativeHistory
			,GetDate()
			,'Cataloger')
	SET @ProvenanceId = SCOPE_IDENTITY()
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION
		PRINT 'An error occurred.  Please notify your administrator to check the error database.'
		INSERT INTO [PEDALS_PAC_ERRORS].[dbo].[PAC_ERROR](ErrorNumber, ErrorMessage, CreatedDate)
			VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH
go
IF OBJECT_ID('PAC_CreateProvenance') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_CreateProvenance >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_CreateProvenance >>>'
go


/* 
 * PROCEDURE: PAC_CreateProvenanceVariant 
 */

CREATE PROCEDURE [PAC_CreateProvenanceVariant]
	@ProvenanceId INT,
	@ProvenanceVariantQualifier VARCHAR (25),
	@ProvenanceVariantName VARCHAR (100)	
AS
BEGIN
	TRY
	SET NOCOUNT ON
		BEGIN TRANSACTION
		INSERT INTO [PeDALS_DLB].[dbo].[ProvenanceVariant]
			([ProvenanceId]
			,[ProvenanceVariantQualifierId]
			,[ProvenanceVariantName]
			,[CreatedDate]
			,[CreatedBy])
     VALUES
			(@ProvenanceId
			,@ProvenanceVariantQualifier
			,@ProvenanceVariantName
			,GETDATE()
			,'Cataloger')
	COMMIT TRANSACTION
	END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION
		PRINT 'An error occurred.  Please notify your administrator to check the error database.'
		INSERT INTO [PEDALS_PAC_ERRORS].[dbo].[PAC_ERROR](ErrorNumber, ErrorMessage, CreatedDate)
			VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH
go
IF OBJECT_ID('PAC_CreateProvenanceVariant') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_CreateProvenanceVariant >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_CreateProvenanceVariant >>>'
go


/* 
 * PROCEDURE: PAC_CreateSeries 
 */

CREATE PROCEDURE [PAC_CreateSeries]
	(@SeriesTitle VARCHAR (250),
	@SeriesDescription VARCHAR (MAX),
	@SeriesOpenDate VARCHAR(50),
	@SeriesStartYear INT,
	@SeriesEndYear INT,
	@SeriesDateRangeDescription VARCHAR (100),
	@SeriesExtentDescription VARCHAR (1024),	
	@SeriesArrangement VARCHAR (1024),
	@SeriesRestrictionDescription VARCHAR (1024),
	@SeriesGeneralNote VARCHAR (MAX),
	@SeriesIdentifier VARCHAR (50),
	@SeriesActivity VARCHAR (50),
	@ProvenanceId INT,
	@SeriesId INT OUTPUT)
AS
BEGIN
	TRY
	SET NOCOUNT ON
	BEGIN TRANSACTION	
	INSERT INTO [PeDALS_DLB].[dbo].[Series]
           ([SeriesTitle]
           ,[SeriesDescription]
           ,[SeriesOpenDate]
           ,[SeriesStartYear]
  		   ,[SeriesEndYear]
           ,[SeriesDateRangeDescription]
           ,[SeriesExtentDescription]
           ,[SeriesArrangement]
           ,[SeriesRestrictionDescription]
           ,[SeriesGeneralNote]
           ,[SeriesIdentifier]
           ,[SeriesActivity]
           ,[ProvenanceId]
           ,[CreatedDate]
           ,[CreatedBy])
     VALUES
           (@SeriesTitle
           ,@SeriesDescription
           ,@SeriesOpenDate
           ,@SeriesStartYear
		   ,@SeriesEndYear
           ,@SeriesDateRangeDescription
           ,@SeriesExtentDescription
           ,@SeriesArrangement
           ,@SeriesRestrictionDescription
           ,@SeriesGeneralNote
           ,@SeriesIdentifier
           ,@SeriesActivity
           ,@ProvenanceId
           ,GETDATE()
           ,'Cataloger')
	SET @SeriesId = SCOPE_IDENTITY()
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION
		PRINT 'An error occurred.  Please notify your administrator to check the error database.'
		INSERT INTO [PEDALS_PAC_ERRORS].[dbo].[PAC_ERROR](ErrorNumber, ErrorMessage, CreatedDate)
			VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH
go
IF OBJECT_ID('PAC_CreateSeries') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_CreateSeries >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_CreateSeries >>>'
go


/* 
 * PROCEDURE: PAC_CreateSeriesAcquisition 
 */

CREATE PROCEDURE [PAC_CreateSeriesAcquisition]
	(@SeriesId INT,
	@SeriesAcquisitionReceivedDate DATETIME,
	@SeriesAcquisitionIngestDate DATETIME,
	@SeriesAcquisitionAccessionNumber VARCHAR (50),
	@SeriesAcquisitionTransferAuthority VARCHAR (50),
	@SeriesAcquisitionSource VARCHAR (100),	
	@SeriesAcquisitionDirectory VARCHAR (255),
	@AcqId INT OUTPUT)

AS
BEGIN
	TRY
	SET NOCOUNT ON
	BEGIN TRANSACTION
		INSERT INTO [PeDALS_DLB].[dbo].[SeriesAcquisition]
           ([SeriesId]
		   ,[SeriesAcquisitionReceivedDate]
           ,[SeriesAcquisitionIngestDate]
           ,[SeriesAcquisitionReadyToIngest]
           ,[SeriesAcquisitionIngested]
		   ,[SeriesAcquisitionAccessionNumber]
           ,[SeriesAcquisitionTransferAuthority]
           ,[SeriesAcquisitionSource]
		   ,[SeriesAcquisitionDirectory]
           ,[CreatedDate]
           ,[CreatedBy])
     VALUES
           (@SeriesId
		   ,@SeriesAcquisitionReceivedDate
           ,@SeriesAcquisitionIngestDate
           ,'False'
           ,'False'
		   ,@SeriesAcquisitionAccessionNumber
           ,@SeriesAcquisitionTransferAuthority
           ,@SeriesAcquisitionSource
		   ,@SeriesAcquisitionDirectory	
           ,GETDATE()
           ,'Cataloger')
	SET @AcqId = SCOPE_IDENTITY()
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION
		PRINT 'An error occurred.  Please notify your administrator to check the error database.'
		INSERT INTO [PEDALS_PAC_ERRORS].[dbo].[PAC_ERROR](ErrorNumber, ErrorMessage, CreatedDate)
			VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH
go
IF OBJECT_ID('PAC_CreateSeriesAcquisition') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_CreateSeriesAcquisition >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_CreateSeriesAcquisition >>>'
go


/* 
 * PROCEDURE: PAC_CreateSeriesKeyword 
 */

CREATE PROCEDURE [PAC_CreateSeriesKeyword]
	(@SeriesId INT,
	@SeriesKeyword VARCHAR (50))
AS
BEGIN
	TRY
		SET NOCOUNT ON
		BEGIN TRANSACTION
		INSERT INTO [PeDALS_DLB].[dbo].[SeriesKeyword]
           ([SeriesId]
			,[SeriesKeyword]
			,[CreatedDate]
			,[CreatedBy])
		VALUES
           (@SeriesId
			,@SeriesKeyword
			,GetDate()
			,'Cataloger')
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION
		PRINT 'An error occurred.  Please notify your administrator to check the error database.'
		INSERT INTO [PEDALS_PAC_ERRORS].[dbo].[PAC_ERROR](ErrorNumber, ErrorMessage, CreatedDate)
			VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH
go
IF OBJECT_ID('PAC_CreateSeriesKeyword') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_CreateSeriesKeyword >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_CreateSeriesKeyword >>>'
go


/* 
 * PROCEDURE: PAC_CreateSeriesLocation 
 */

CREATE PROCEDURE [PAC_CreateSeriesLocation]
	(@SeriesId INT,
	@SeriesGeoLocationValue VARCHAR (100))
AS
BEGIN
	TRY
		SET NOCOUNT ON
		BEGIN TRANSACTION
		INSERT INTO [PeDALS_DLB].[dbo].[SeriesLocation]
           ([SeriesId]
			,[SeriesGeoLocationValue]
			,[CreatedDate]
			,[CreatedBy])
		VALUES
           (@SeriesId
			,@SeriesGeoLocationValue
			,GetDate()
			,'Cataloger')
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION
		PRINT 'An error occurred.  Please notify your administrator to check the error database.'
		INSERT INTO [PEDALS_PAC_ERRORS].[dbo].[PAC_ERROR](ErrorNumber, ErrorMessage, CreatedDate)
			VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH
go
IF OBJECT_ID('PAC_CreateSeriesLocation') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_CreateSeriesLocation >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_CreateSeriesLocation >>>'
go


/* 
 * PROCEDURE: PAC_CreateSeriesSubject 
 */

CREATE PROCEDURE [PAC_CreateSeriesSubject]
	(@SeriesId INT,
	 @SeriesSubjectQualifier VARCHAR (25),
	 @SeriesSubjectName VARCHAR(100))
AS
BEGIN
	TRY
		SET NOCOUNT ON
		BEGIN TRANSACTION
		INSERT INTO [PeDALS_DLB].[dbo].[SeriesSubject]
           ([SeriesId]
			,[SeriesSubjectQualifier]
			,[SeriesSubjectName]
			,[CreatedDate]
			,[CreatedBy])
		VALUES
           (@SeriesId
			,@SeriesSubjectQualifier
			,@SeriesSubjectName
			,GetDate()
			,'Cataloger')
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION
		PRINT 'An error occurred.  Please notify your administrator to check the error database.'
		INSERT INTO [PEDALS_PAC_ERRORS].[dbo].[PAC_ERROR](ErrorNumber, ErrorMessage, CreatedDate)
			VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH
go
IF OBJECT_ID('PAC_CreateSeriesSubject') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_CreateSeriesSubject >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_CreateSeriesSubject >>>'
go


/* 
 * PROCEDURE: PAC_DeleteProvenanceVariant 
 */

CREATE PROCEDURE [PAC_DeleteProvenanceVariant] 
	-- Add the parameters for the stored procedure here
	@ProvenanceVariantId INT
AS
BEGIN
	SET NOCOUNT ON;
	DELETE FROM ProvenanceVariant WHERE ProvenanceVariantId = @ProvenanceVariantId
END
go
IF OBJECT_ID('PAC_DeleteProvenanceVariant') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_DeleteProvenanceVariant >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_DeleteProvenanceVariant >>>'
go


/* 
 * PROCEDURE: PAC_DeleteSeriesKeyword 
 */

CREATE PROCEDURE [PAC_DeleteSeriesKeyword] 
	-- Add the parameters for the stored procedure here
	@SeriesKeywordId INT
AS
BEGIN
	SET NOCOUNT ON;
	DELETE FROM SeriesKeyword WHERE SeriesKeywordId = @SeriesKeywordId
END
go
IF OBJECT_ID('PAC_DeleteSeriesKeyword') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_DeleteSeriesKeyword >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_DeleteSeriesKeyword >>>'
go


/* 
 * PROCEDURE: PAC_DeleteSeriesLocation 
 */

CREATE PROCEDURE [PAC_DeleteSeriesLocation] 
	-- Add the parameters for the stored procedure here
	@SeriesLocationId INT
AS
BEGIN
	SET NOCOUNT ON;
	DELETE FROM SeriesLocation WHERE SeriesLocationId = @SeriesLocationId
END
go
IF OBJECT_ID('PAC_DeleteSeriesLocation') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_DeleteSeriesLocation >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_DeleteSeriesLocation >>>'
go


/* 
 * PROCEDURE: PAC_DeleteSeriesSubject 
 */

CREATE PROCEDURE [PAC_DeleteSeriesSubject] 
	-- Add the parameters for the stored procedure here
	@SeriesSubjectId INT
AS
BEGIN
	SET NOCOUNT ON;
	DELETE FROM SeriesSubject WHERE SeriesSubjectId = @SeriesSubjectId
END
go
IF OBJECT_ID('PAC_DeleteSeriesSubject') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_DeleteSeriesSubject >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_DeleteSeriesSubject >>>'
go


/* 
 * PROCEDURE: PAC_GetItemCount 
 */

CREATE PROCEDURE [PAC_GetItemCount]
	@AcqId INT,
	@ItemCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
	SET @ItemCount = (SELECT COUNT(*) 
	FROM SeriesAcquisitionItem INNER JOIN
	Item ON SeriesAcquisitionItem.ItemId = Item.ItemId
	WHERE SeriesAcquisitionItem.SeriesAcquisitionId = @AcqId)

END
go
IF OBJECT_ID('PAC_GetItemCount') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_GetItemCount >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_GetItemCount >>>'
go


/* 
 * PROCEDURE: PAC_GetItemsList 
 */

CREATE PROCEDURE [dbo].[PAC_GetItemsList]
	(@AcqId int, 
	@ItemPull varchar(25),
	@ItemCount int)
AS

IF @ItemPull = 'no' 
BEGIN
	SET NOCOUNT ON
	SELECT TOP (@ItemCount) ItemId, ItemTitle FROM Item ORDER BY NEWID()
END
ELSE
IF @ItemPull = 'rnd'
BEGIN
	DECLARE @i INT
	SET @i = (@ItemCount * 0.10)
	SELECT TOP (@i) ItemId, ItemTitle FROM Item ORDER BY NEWID()
END
ELSE
	SELECT ItemId, ItemTitle FROM Item ORDER BY ItemTitle
go
IF OBJECT_ID('PAC_GetItemsList') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_GetItemsList >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_GetItemsList >>>'
go


/* 
 * PROCEDURE: PAC_GetProvenanceNamesAndVariants 
 */

CREATE PROCEDURE [PAC_GetProvenanceNamesAndVariants]
AS
BEGIN
	TRY
	SET NOCOUNT ON
		SELECT     ProvenanceId, ProvenanceName AS Name
			FROM         dbo.Provenance
			UNION
			SELECT     TOP (100) PERCENT ProvenanceId, ProvenanceVariantName AS Name
			FROM         dbo.ProvenanceVariant
			ORDER BY Name
	END TRY
BEGIN CATCH
      	PRINT 'An error occurred.  Please notify your administrator to check the error database.'
		INSERT INTO [PEDALS_PAC_ERRORS].[dbo].[PAC_ERROR](ErrorNumber, ErrorMessage, CreatedDate)
			VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH
go
IF OBJECT_ID('PAC_GetProvenanceNamesAndVariants') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_GetProvenanceNamesAndVariants >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_GetProvenanceNamesAndVariants >>>'
go


/* 
 * PROCEDURE: PAC_GetProvenances 
 */

CREATE PROCEDURE [PAC_GetProvenances]
AS
BEGIN
	TRY
	SET NOCOUNT ON
  		SELECT * FROM Provenance ORDER BY ProvenanceName 
	END TRY
BEGIN CATCH
		PRINT 'An error occurred.  Please notify your administrator to check the error database.'
		INSERT INTO [PEDALS_PAC_ERRORS].[dbo].[PAC_ERROR](ErrorNumber, ErrorMessage, CreatedDate)
			VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH
go
IF OBJECT_ID('PAC_GetProvenances') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_GetProvenances >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_GetProvenances >>>'
go


/* 
 * PROCEDURE: PAC_GetSeries 
 */

CREATE PROCEDURE [dbo].[PAC_GetSeries]
AS
BEGIN
	TRY
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON
	SELECT     TOP (100) PERCENT [PeDALS_DLB].[dbo].[Series].[SeriesTitle], [PeDALS_DLB].[dbo].[Series].[SeriesId], [PeDALS_DLB].[dbo].[Provenance].[ProvenanceName]
	FROM         [PeDALS_DLB].[dbo].[Provenance] RIGHT OUTER JOIN
                      [PeDALS_DLB].[dbo].[Series] ON [PeDALS_DLB].[dbo].[Provenance].[ProvenanceId] = [PeDALS_DLB].[dbo].[Series].[ProvenanceId]
	ORDER BY [PeDALS_DLB].[dbo].[Provenance].[ProvenanceName], [PeDALS_DLB].[dbo].[Series].[SeriesTitle]
END TRY
BEGIN CATCH
		PRINT 'An error occurred.  Please notify your administrator to check the error database.'
		INSERT INTO [PEDALS_PAC_ERRORS].[dbo].[PAC_ERROR](ErrorNumber, ErrorMessage, CreatedDate)
			VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH
go
IF OBJECT_ID('PAC_GetSeries') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_GetSeries >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_GetSeries >>>'
go


/* 
 * PROCEDURE: PAC_GetSeriesAcquisitions 
 */

CREATE PROCEDURE [dbo].[PAC_GetSeriesAcquisitions]
AS
BEGIN
	TRY
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON
	SELECT     TOP (100) PERCENT [PeDALS_DLB].[dbo].[Provenance].[ProvenanceName], [PeDALS_DLB].[dbo].[Series].[SeriesTitle], CONVERT(VARCHAR,[PeDALS_DLB].[dbo].[SeriesAcquisition].[SeriesAcquisitionIngestDate],101) AS SeriesAcquisitionIngestDate, CONVERT(VARCHAR,[PeDALS_DLB].[dbo].[SeriesAcquisition].[SeriesAcquisitionReceivedDate],101) AS SeriesAcquisitionReceivedDate, 
                      [PeDALS_DLB].[dbo].[SeriesAcquisition].[SeriesAcquisitionId], [PeDALS_DLB].[dbo].[SeriesAcquisition].[SeriesAcquisitionAccessionNumber]
		   FROM         [PeDALS_DLB].[dbo].[Provenance] INNER JOIN
                      [PeDALS_DLB].[dbo].[Series] ON [PeDALS_DLB].[dbo].[Provenance].[ProvenanceId] = [PeDALS_DLB].[dbo].[Series].[ProvenanceId] INNER JOIN
                      [PeDALS_DLB].[dbo].[SeriesAcquisition] ON [PeDALS_DLB].[dbo].[Series].[SeriesId] = [PeDALS_DLB].[dbo].[SeriesAcquisition].[SeriesId]
		   ORDER BY [PeDALS_DLB].[dbo].[Provenance].[ProvenanceName]
END TRY
BEGIN CATCH
		PRINT 'An error occurred.  Please notify your administrator to check the error database.'
		INSERT INTO [PEDALS_PAC_ERRORS].[dbo].[PAC_ERROR](ErrorNumber, ErrorMessage, CreatedDate)
			VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH
go
IF OBJECT_ID('PAC_GetSeriesAcquisitions') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_GetSeriesAcquisitions >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_GetSeriesAcquisitions >>>'
go


/* 
 * PROCEDURE: PAC_UpdateProvenance 
 */

CREATE PROCEDURE [PAC_UpdateProvenance]
	@ProvenanceId INT,
	@ProvenanceName VARCHAR (100),
	@ProvenanceAdministrativeHistory VARCHAR (MAX)
AS
BEGIN
	TRY
	SET NOCOUNT ON
	BEGIN TRANSACTION
		UPDATE [PeDALS_DLB].[dbo].[Provenance]
		SET [PeDALS_DLB].[dbo].[Provenance].[ProvenanceName] = @ProvenanceName,
			[PeDALS_DLB].[dbo].[Provenance].[ProvenanceAdministrativeHistory] = @ProvenanceAdministrativeHistory,
			[PeDALS_DLB].[dbo].[Provenance].[ModifiedDate] = GetDate(),
			[PeDALS_DLB].[dbo].[Provenance].[ModifiedBy] = 'Cataloger'
	WHERE [PeDALS_DLB].[dbo].[Provenance].[ProvenanceId] = @ProvenanceId	
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION
		PRINT 'An error occurred.  Please notify your administrator to check the error database.'
		INSERT INTO [PEDALS_PAC_ERRORS].[dbo].[PAC_ERROR](ErrorNumber, ErrorMessage, CreatedDate)
			VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH
go
IF OBJECT_ID('PAC_UpdateProvenance') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_UpdateProvenance >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_UpdateProvenance >>>'
go


/* 
 * PROCEDURE: PAC_UpdateProvenanceVariant 
 */

CREATE PROCEDURE [PAC_UpdateProvenanceVariant]
	@ProvenanceVariantId INT,
	@ProvenanceId INT,
	@ProvenanceVariantQualifierId VARCHAR (25),
	@ProvenanceVariantName VARCHAR (100)	
AS
BEGIN
	TRY
	SET NOCOUNT ON
	BEGIN TRANSACTION
IF @ProvenanceVariantId is null
		INSERT INTO [PeDALS_DLB].[dbo].[ProvenanceVariant]
			([ProvenanceId]
			,[ProvenanceVariantQualifierId]
			,[ProvenanceVariantName]
			,[CreatedDate]
			,[CreatedBy])
     VALUES
			(@ProvenanceId
			,@ProvenanceVariantQualifierId
			,@ProvenanceVariantName
			,GETDATE()
			,'Cataloger')
ELSE
		UPDATE ProvenanceVariant
		SET	ProvenanceVariantQualifierId = @ProvenanceVariantQualifierId,
			ProvenanceVariantName = @ProvenanceVariantName,
			ModifiedDate = GetDate(),
			ModifiedBy = 'Cataloger'
	WHERE (ProvenanceVariantId = @ProvenanceVariantId)
	COMMIT TRANSACTION
END TRY

BEGIN CATCH
      ROLLBACK TRANSACTION
		PRINT 'An error occurred.  Please notify your administrator to check the error database.'
		INSERT INTO [PEDALS_PAC_ERRORS].[dbo].[PAC_ERROR](ErrorNumber, ErrorMessage, CreatedDate)
			VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH
go
IF OBJECT_ID('PAC_UpdateProvenanceVariant') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_UpdateProvenanceVariant >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_UpdateProvenanceVariant >>>'
go


/* 
 * PROCEDURE: PAC_UpdateSeries 
 */

CREATE PROCEDURE [PAC_UpdateSeries]
	  @SeriesId int,
	  @SeriesTitle varchar(250),
      @SeriesDescription varchar(max),
      @SeriesOpenDate varchar(50),
      @SeriesStartYear int,
      @SeriesEndYear int,
      @SeriesDateRangeDescription varchar(100),
      @SeriesExtentDescription varchar(1024),
      @SeriesArrangement varchar(1024),
      @SeriesRestrictionDescription varchar(1024),
      @SeriesGeneralNote varchar(max),
      @SeriesIdentifier varchar(50),
      @SeriesActivity varchar(50),
      @ProvenanceId int
AS
BEGIN
	TRY
	SET NOCOUNT ON
	BEGIN TRANSACTION
		UPDATE [PeDALS_DLB].[dbo].[Series]
		SET [PeDALS_DLB].[dbo].[Series].[SeriesTitle] = @SeriesTitle,
			[PeDALS_DLB].[dbo].[Series].[SeriesDescription] = @SeriesDescription,
			[PeDALS_DLB].[dbo].[Series].[SeriesOpenDate] = @SeriesOpenDate,
			[PeDALS_DLB].[dbo].[Series].[SeriesStartYear] = @SeriesStartYear,
			[PeDALS_DLB].[dbo].[Series].[SeriesEndYear] = @SeriesEndYear,
			[PeDALS_DLB].[dbo].[Series].[SeriesDateRangeDescription] = @SeriesDateRangeDescription,
			[PeDALS_DLB].[dbo].[Series].[SeriesExtentDescription] = @SeriesExtentDescription,
			[PeDALS_DLB].[dbo].[Series].[SeriesArrangement] = @SeriesArrangement,
			[PeDALS_DLB].[dbo].[Series].[SeriesRestrictionDescription] = @SeriesRestrictionDescription,
			[PeDALS_DLB].[dbo].[Series].[SeriesGeneralNote] = @SeriesGeneralNote,
			[PeDALS_DLB].[dbo].[Series].[SeriesIdentifier] = @SeriesIdentifier,
			[PeDALS_DLB].[dbo].[Series].[SeriesActivity] = @SeriesActivity,
			[PeDALS_DLB].[dbo].[Series].[ProvenanceId] = @ProvenanceId,
			[PeDALS_DLB].[dbo].[Series].[ModifiedDate] = GetDate(),
			[PeDALS_DLB].[dbo].[Series].[ModifiedBy] = 'Cataloger'
	WHERE [PeDALS_DLB].[dbo].[Series].[SeriesId] = @SeriesId
		AND
		  [PeDALS_DLB].[dbo].[Series].[ProvenanceId] = @ProvenanceId
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION
		PRINT 'An error occurred.  Please notify your administrator to check the error database.'
		INSERT INTO [PEDALS_PAC_ERRORS].[dbo].[PAC_ERROR](ErrorNumber, ErrorMessage, CreatedDate)
			VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH
go
IF OBJECT_ID('PAC_UpdateSeries') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_UpdateSeries >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_UpdateSeries >>>'
go


/* 
 * PROCEDURE: PAC_UpdateSeriesAcquisition 
 */

CREATE PROCEDURE [PAC_UpdateSeriesAcquisition]
	(@SeriesAcquisitionId INT,
	 @SeriesAcquisitionReceivedDate DATETIME,
	 @SeriesAcquisitionIngestDate DATETIME,
	 @SeriesAcquisitionAccessionNumber VARCHAR (50),
	 @SeriesAcquisitionTransferAuthority VARCHAR (50),
	 @SeriesAcquisitionSource VARCHAR (100),
	 @SeriesAcquisitionDirectory VARCHAR (255))

AS
BEGIN
	TRY
	SET NOCOUNT ON
	BEGIN TRANSACTION
		UPDATE [PeDALS_DLB].[dbo].[SeriesAcquisition]
		 SET [SeriesAcquisitionReceivedDate] = @SeriesAcquisitionReceivedDate
		  ,[SeriesAcquisitionIngestDate] = @SeriesAcquisitionIngestDate
		  ,[SeriesAcquisitionAccessionNumber] = @SeriesAcquisitionAccessionNumber
		  ,[SeriesAcquisitionTransferAuthority] = @SeriesAcquisitionTransferAuthority
		  ,[SeriesAcquisitionSource] = @SeriesAcquisitionSource
	      ,[SeriesAcquisitionDirectory] = @SeriesAcquisitionDirectory
		  ,[ModifiedDate] = GETDATE()
		  ,[ModifiedBy] = 'Cataloger'
	WHERE [SeriesAcquisitionId] = @SeriesAcquisitionId	
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION
		PRINT 'An error occurred.  Please notify your administrator to check the error database.'
		INSERT INTO [PEDALS_PAC_ERRORS].[dbo].[PAC_ERROR](ErrorNumber, ErrorMessage, CreatedDate)
			VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH
go
IF OBJECT_ID('PAC_UpdateSeriesAcquisition') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_UpdateSeriesAcquisition >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_UpdateSeriesAcquisition >>>'
go


/* 
 * PROCEDURE: PAC_UpdateSeriesKeyword 
 */

CREATE PROCEDURE [PAC_UpdateSeriesKeyword]
	  @SeriesKeywordId int,
	  @SeriesId int,
	  @SeriesKeyword varchar(50)
AS
BEGIN
	TRY
	SET NOCOUNT ON
	BEGIN TRANSACTION
IF @SeriesKeywordId is null
		INSERT INTO [PeDALS_DLB].[dbo].[SeriesKeyword]
			([SeriesId]
			,[SeriesKeyword]
			,[CreatedDate]
			,[CreatedBy])
     VALUES
			(@SeriesId
			,@SeriesKeyword
			,GETDATE()
			,'Cataloger')
ELSE
		UPDATE [PeDALS_DLB].[dbo].[SeriesKeyword]
		SET [PeDALS_DLB].[dbo].[SeriesKeyword].[SeriesKeyword] = @SeriesKeyword,
			[PeDALS_DLB].[dbo].[SeriesKeyword].[ModifiedDate] = GetDate(),
			[PeDALS_DLB].[dbo].[SeriesKeyword].[ModifiedBy] = 'Cataloger'
	WHERE [PeDALS_DLB].[dbo].[SeriesKeyword].[SeriesKeywordId] = @SeriesKeywordId
	COMMIT TRANSACTION
END TRY

BEGIN CATCH
      ROLLBACK TRANSACTION
		PRINT 'An error occurred.  Please notify your administrator to check the error database.'
		INSERT INTO [PEDALS_PAC_ERRORS].[dbo].[PAC_ERROR](ErrorNumber, ErrorMessage, CreatedDate)
			VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH
go
IF OBJECT_ID('PAC_UpdateSeriesKeyword') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_UpdateSeriesKeyword >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_UpdateSeriesKeyword >>>'
go


/* 
 * PROCEDURE: PAC_UpdateSeriesLocation 
 */

CREATE PROCEDURE [PAC_UpdateSeriesLocation]
      @SeriesLocationId int,
	  @SeriesId int,
	  @SeriesGeoLocationValue varchar(100)

AS
BEGIN
	TRY
	SET NOCOUNT ON
	BEGIN TRANSACTION
IF @SeriesLocationId is null
		INSERT INTO [PeDALS_DLB].[dbo].[SeriesLocation]
			([SeriesId]
			,[SeriesGeoLocationValue]
			,[CreatedDate]
			,[CreatedBy])
     VALUES
			(@SeriesId
			,@SeriesGeoLocationValue
			,GETDATE()
			,'Cataloger')
ELSE
		UPDATE [PeDALS_DLB].[dbo].[SeriesLocation]
		SET [PeDALS_DLB].[dbo].[SeriesLocation].[SeriesGeoLocationValue] = @SeriesGeoLocationValue,
			[PeDALS_DLB].[dbo].[SeriesLocation].[ModifiedDate] = GetDate(),
			[PeDALS_DLB].[dbo].[SeriesLocation].[ModifiedBy] = 'Cataloger'
	WHERE [PeDALS_DLB].[dbo].[SeriesLocation].[SeriesLocationId] = @SeriesLocationId
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION
		PRINT 'An error occurred.  Please notify your administrator to check the error database.'
		INSERT INTO [PEDALS_PAC_ERRORS].[dbo].[PAC_ERROR](ErrorNumber, ErrorMessage, CreatedDate)
			VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH
go
IF OBJECT_ID('PAC_UpdateSeriesLocation') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_UpdateSeriesLocation >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_UpdateSeriesLocation >>>'
go


/* 
 * PROCEDURE: PAC_UpdateSeriesSubject 
 */

CREATE PROCEDURE [PAC_UpdateSeriesSubject]
      @SeriesSubjectId int,
	  @SeriesId int,
	  @SeriesSubjectQualifier varchar(25),
	  @SeriesSubjectName varchar(100)
AS
BEGIN
	TRY
	SET NOCOUNT ON
	BEGIN TRANSACTION
IF @SeriesSubjectId is null
		INSERT INTO [PeDALS_DLB].[dbo].[SeriesSubject]
			([SeriesId]
			,[SeriesSubjectQualifier]
			,[SeriesSubjectName]
			,[CreatedDate]
			,[CreatedBy])
     VALUES
			(@SeriesId
			,@SeriesSubjectQualifier
			,@SeriesSubjectName
			,GETDATE()
			,'Cataloger')
ELSE
		UPDATE [PeDALS_DLB].[dbo].[SeriesSubject]
		SET [PeDALS_DLB].[dbo].[SeriesSubject].[SeriesSubjectQualifier] = @SeriesSubjectQualifier,
			[PeDALS_DLB].[dbo].[SeriesSubject].[SeriesSubjectName] = @SeriesSubjectName,
			[PeDALS_DLB].[dbo].[SeriesSubject].[ModifiedDate] = GetDate(),
			[PeDALS_DLB].[dbo].[SeriesSubject].[ModifiedBy] = 'Cataloger'
	WHERE [PeDALS_DLB].[dbo].[SeriesSubject].[SeriesSubjectId] = @SeriesSubjectId
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION
		PRINT 'An error occurred.  Please notify your administrator to check the error database.'
		INSERT INTO [PEDALS_PAC_ERRORS].[dbo].[PAC_ERROR](ErrorNumber, ErrorMessage, CreatedDate)
			VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH
go
IF OBJECT_ID('PAC_UpdateSeriesSubject') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE PAC_UpdateSeriesSubject >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE PAC_UpdateSeriesSubject >>>'
go


/* 
 * PROCEDURE: pdl_GetInitialValues 
 */

CREATE PROCEDURE [pdl_GetInitialValues] 
	@ProvenanceId INT,
	@SeriesId INT,
	@SeriesAcquisitionId INT
AS
BEGIN

	DECLARE @temp TABLE
		(
			ItemId INT
		)
	
	INSERT INTO @temp
		SELECT ISNULL(MAX(ItemId), 0) FROM Item WHERE SeriesId = @SeriesId
		
	SELECT
		P.ProvenanceName,
		S.SeriesTitle,
		S.SeriesIdentifier,
		SA.SeriesAcquisitionSource,
		SA.SeriesAcquisitionDirectory,
		SA.SeriesAcquisitionAccessionNumber,
		SA.SeriesAcquisitionIngestDate,
		SA.SeriesAcquisitionReceivedDate,
		I.ItemId
	FROM 
		dbo.Provenance P,
		dbo.Series S,
		dbo.SeriesAcquisition SA,
		@temp I
	WHERE 
		P.ProvenanceId = @ProvenanceId AND
		S.SeriesId = @SeriesId AND
		SA.SeriesAcquisitionId = @SeriesAcquisitionId
	FOR XML AUTO--, XMLDATA
END
go
IF OBJECT_ID('pdl_GetInitialValues') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE pdl_GetInitialValues >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE pdl_GetInitialValues >>>'
go


/* 
 * PROCEDURE: pdl_ReadyToIngest 
 */

CREATE Procedure [pdl_ReadyToIngest]
AS
	BEGIN
		SELECT Top 1 SeriesAcquisitionId, SeriesId
		INTO #TempAcquisitionSeries
		FROM SeriesAcquisition
		WHERE  SeriesAcquisitionReadyToIngest = 'True'
		AND	SeriesAcquisitionIngested = 'False'
	
	UPDATE SeriesAcquisition
	SET SeriesAcquisitionIngested = 'True'
	WHERE SeriesAcquisitionId = (SELECT SeriesAcquisitionId FROM #TempAcquisitionSeries)

	SELECT SeriesAcquisitionId, SeriesId
	FROM #TempAcquisitionSeries
	FOR XML AUTO--, XMLDATA

	DROP TABLE #TempAcquisitionSeries
	
	END
go
IF OBJECT_ID('pdl_ReadyToIngest') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE pdl_ReadyToIngest >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE pdl_ReadyToIngest >>>'
go


/* 
 * PROCEDURE: sp_alterdiagram 
 */


	CREATE PROCEDURE sp_alterdiagram
	(
		@diagramname 	sysname,
		@owner_id	int	= null,
		@version 	int,
		@definition 	varbinary(max)
	)
	WITH EXECUTE AS 'dbo'
	AS
	BEGIN
		set nocount on
	
		declare @theId 			int
		declare @retval 		int
		declare @IsDbo 			int
		
		declare @UIDFound 		int
		declare @DiagId			int
		declare @ShouldChangeUID	int
	
		if(@diagramname is null)
		begin
			RAISERROR ('Invalid ARG', 16, 1)
			return -1
		end
	
		execute as caller;
		select @theId = DATABASE_PRINCIPAL_ID();	 
		select @IsDbo = IS_MEMBER(N'db_owner'); 
		if(@owner_id is null)
			select @owner_id = @theId;
		revert;
	
		select @ShouldChangeUID = 0
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 
		
		if(@DiagId IS NULL or (@IsDbo = 0 and @theId <> @UIDFound))
		begin
			RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1);
			return -3
		end
	
		if(@IsDbo <> 0)
		begin
			if(@UIDFound is null or USER_NAME(@UIDFound) is null) -- invalid principal_id
			begin
				select @ShouldChangeUID = 1 ;
			end
		end

		-- update dds data			
		update dbo.sysdiagrams set definition = @definition where diagram_id = @DiagId ;

		-- change owner
		if(@ShouldChangeUID = 1)
			update dbo.sysdiagrams set principal_id = @theId where diagram_id = @DiagId ;

		-- update dds version
		if(@version is not null)
			update dbo.sysdiagrams set version = @version where diagram_id = @DiagId ;

		return 0
	END
go
IF OBJECT_ID('sp_alterdiagram') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE sp_alterdiagram >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE sp_alterdiagram >>>'
go


/* 
 * PROCEDURE: sp_creatediagram 
 */


	CREATE PROCEDURE sp_creatediagram
	(
		@diagramname 	sysname,
		@owner_id		int	= null, 	
		@version 		int,
		@definition 	varbinary(max)
	)
	WITH EXECUTE AS 'dbo'
	AS
	BEGIN
		set nocount on
	
		declare @theId int
		declare @retval int
		declare @IsDbo	int
		declare @userName sysname
		if(@version is null or @diagramname is null)
		begin
			RAISERROR (N'E_INVALIDARG', 16, 1);
			return -1
		end
	
		execute as caller;
		select @theId = DATABASE_PRINCIPAL_ID(); 
		select @IsDbo = IS_MEMBER(N'db_owner');
		revert; 
		
		if @owner_id is null
		begin
			select @owner_id = @theId;
		end
		else
		begin
			if @theId <> @owner_id
			begin
				if @IsDbo = 0
				begin
					RAISERROR (N'E_INVALIDARG', 16, 1);
					return -1
				end
				select @theId = @owner_id
			end
		end
		-- next 2 line only for test, will be removed after define name unique
		if EXISTS(select diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @diagramname)
		begin
			RAISERROR ('The name is already used.', 16, 1);
			return -2
		end
	
		insert into dbo.sysdiagrams(name, principal_id , version, definition)
				VALUES(@diagramname, @theId, @version, @definition) ;
		
		select @retval = @@IDENTITY 
		return @retval
	END
go
IF OBJECT_ID('sp_creatediagram') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE sp_creatediagram >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE sp_creatediagram >>>'
go


/* 
 * PROCEDURE: sp_dropdiagram 
 */


	CREATE PROCEDURE sp_dropdiagram
	(
		@diagramname 	sysname,
		@owner_id	int	= null
	)
	WITH EXECUTE AS 'dbo'
	AS
	BEGIN
		set nocount on
		declare @theId 			int
		declare @IsDbo 			int
		
		declare @UIDFound 		int
		declare @DiagId			int
	
		if(@diagramname is null)
		begin
			RAISERROR ('Invalid value', 16, 1);
			return -1
		end
	
		EXECUTE AS CALLER;
		select @theId = DATABASE_PRINCIPAL_ID();
		select @IsDbo = IS_MEMBER(N'db_owner'); 
		if(@owner_id is null)
			select @owner_id = @theId;
		REVERT; 
		
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 
		if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))
		begin
			RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1)
			return -3
		end
	
		delete from dbo.sysdiagrams where diagram_id = @DiagId;
	
		return 0;
	END
go
IF OBJECT_ID('sp_dropdiagram') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE sp_dropdiagram >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE sp_dropdiagram >>>'
go


/* 
 * PROCEDURE: sp_helpdiagramdefinition 
 */


	CREATE PROCEDURE sp_helpdiagramdefinition
	(
		@diagramname 	sysname,
		@owner_id	int	= null 		
	)
	WITH EXECUTE AS N'dbo'
	AS
	BEGIN
		set nocount on

		declare @theId 		int
		declare @IsDbo 		int
		declare @DiagId		int
		declare @UIDFound	int
	
		if(@diagramname is null)
		begin
			RAISERROR (N'E_INVALIDARG', 16, 1);
			return -1
		end
	
		execute as caller;
		select @theId = DATABASE_PRINCIPAL_ID();
		select @IsDbo = IS_MEMBER(N'db_owner');
		if(@owner_id is null)
			select @owner_id = @theId;
		revert; 
	
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname;
		if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId ))
		begin
			RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1);
			return -3
		end

		select version, definition FROM dbo.sysdiagrams where diagram_id = @DiagId ; 
		return 0
	END
go
IF OBJECT_ID('sp_helpdiagramdefinition') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE sp_helpdiagramdefinition >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE sp_helpdiagramdefinition >>>'
go


/* 
 * PROCEDURE: sp_helpdiagrams 
 */


	CREATE PROCEDURE sp_helpdiagrams
	(
		@diagramname sysname = NULL,
		@owner_id int = NULL
	)
	WITH EXECUTE AS N'dbo'
	AS
	BEGIN
		DECLARE @user sysname
		DECLARE @dboLogin bit
		EXECUTE AS CALLER;
			SET @user = USER_NAME();
			SET @dboLogin = CONVERT(bit,IS_MEMBER('db_owner'));
		REVERT;
		SELECT
			[Database] = DB_NAME(),
			[Name] = name,
			[ID] = diagram_id,
			[Owner] = USER_NAME(principal_id),
			[OwnerID] = principal_id
		FROM
			sysdiagrams
		WHERE
			(@dboLogin = 1 OR USER_NAME(principal_id) = @user) AND
			(@diagramname IS NULL OR name = @diagramname) AND
			(@owner_id IS NULL OR principal_id = @owner_id)
		ORDER BY
			4, 5, 1
	END
go
IF OBJECT_ID('sp_helpdiagrams') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE sp_helpdiagrams >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE sp_helpdiagrams >>>'
go


/* 
 * PROCEDURE: sp_renamediagram 
 */


	CREATE PROCEDURE sp_renamediagram
	(
		@diagramname 		sysname,
		@owner_id		int	= null,
		@new_diagramname	sysname
	
	)
	WITH EXECUTE AS 'dbo'
	AS
	BEGIN
		set nocount on
		declare @theId 			int
		declare @IsDbo 			int
		
		declare @UIDFound 		int
		declare @DiagId			int
		declare @DiagIdTarg		int
		declare @u_name			sysname
		if((@diagramname is null) or (@new_diagramname is null))
		begin
			RAISERROR ('Invalid value', 16, 1);
			return -1
		end
	
		EXECUTE AS CALLER;
		select @theId = DATABASE_PRINCIPAL_ID();
		select @IsDbo = IS_MEMBER(N'db_owner'); 
		if(@owner_id is null)
			select @owner_id = @theId;
		REVERT;
	
		select @u_name = USER_NAME(@owner_id)
	
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 
		if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))
		begin
			RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1)
			return -3
		end
	
		-- if((@u_name is not null) and (@new_diagramname = @diagramname))	-- nothing will change
		--	return 0;
	
		if(@u_name is null)
			select @DiagIdTarg = diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @new_diagramname
		else
			select @DiagIdTarg = diagram_id from dbo.sysdiagrams where principal_id = @owner_id and name = @new_diagramname
	
		if((@DiagIdTarg is not null) and  @DiagId <> @DiagIdTarg)
		begin
			RAISERROR ('The name is already used.', 16, 1);
			return -2
		end		
	
		if(@u_name is null)
			update dbo.sysdiagrams set [name] = @new_diagramname, principal_id = @theId where diagram_id = @DiagId
		else
			update dbo.sysdiagrams set [name] = @new_diagramname where diagram_id = @DiagId
		return 0
	END
go
IF OBJECT_ID('sp_renamediagram') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE sp_renamediagram >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE sp_renamediagram >>>'
go


/* 
 * PROCEDURE: sp_upgraddiagrams 
 */


	CREATE PROCEDURE sp_upgraddiagrams
	AS
	BEGIN
		IF OBJECT_ID(N'dbo.sysdiagrams') IS NOT NULL
			return 0;
	
		CREATE TABLE dbo.sysdiagrams
		(
			name sysname NOT NULL,
			principal_id int NOT NULL,	-- we may change it to varbinary(85)
			diagram_id int PRIMARY KEY IDENTITY,
			version int,
	
			definition varbinary(max)
			CONSTRAINT UK_principal_name UNIQUE
			(
				principal_id,
				name
			)
		);


		/* Add this if we need to have some form of extended properties for diagrams */
		/*
		IF OBJECT_ID(N'dbo.sysdiagram_properties') IS NULL
		BEGIN
			CREATE TABLE dbo.sysdiagram_properties
			(
				diagram_id int,
				name sysname,
				value varbinary(max) NOT NULL
			)
		END
		*/

		IF OBJECT_ID(N'dbo.dtproperties') IS NOT NULL
		begin
			insert into dbo.sysdiagrams
			(
				[name],
				[principal_id],
				[version],
				[definition]
			)
			select	 
				convert(sysname, dgnm.[uvalue]),
				DATABASE_PRINCIPAL_ID(N'dbo'),			-- will change to the sid of sa
				0,							-- zero for old format, dgdef.[version],
				dgdef.[lvalue]
			from dbo.[dtproperties] dgnm
				inner join dbo.[dtproperties] dggd on dggd.[property] = 'DtgSchemaGUID' and dggd.[objectid] = dgnm.[objectid]	
				inner join dbo.[dtproperties] dgdef on dgdef.[property] = 'DtgSchemaDATA' and dgdef.[objectid] = dgnm.[objectid]
				
			where dgnm.[property] = 'DtgSchemaNAME' and dggd.[uvalue] like N'_EA3E6268-D998-11CE-9454-00AA00A3F36E_' 
			return 2;
		end
		return 1;
	END
go
IF OBJECT_ID('sp_upgraddiagrams') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE sp_upgraddiagrams >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE sp_upgraddiagrams >>>'
go


/* 
 * PROCEDURE: ComponentInsProc 
 */

CREATE PROCEDURE ComponentInsProc
(
    @ComponentSequence     int                    = NULL,
    @ItemId                int                    = NULL,
    @DigitalFormatId       int                    = NULL,
    @HardwareId            int                    = NULL,
    @OperatingSystemId     int                    = NULL,
    @SoftwareId            int                    = NULL,
    @CreatedDate           datetime,
    @CreatedBy             varchar(50),
    @ModifiedDate          datetime               = NULL,
    @ModifiedBy            varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO Component(ComponentSequence,
                          ItemId,
                          DigitalFormatId,
                          HardwareId,
                          OperatingSystemId,
                          SoftwareId,
                          CreatedDate,
                          CreatedBy,
                          ModifiedDate,
                          ModifiedBy)
    VALUES(@ComponentSequence,
           @ItemId,
           @DigitalFormatId,
           @HardwareId,
           @OperatingSystemId,
           @SoftwareId,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'ComponentInsProc: Cannot insert because primary key value not found in Component '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('ComponentInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ComponentInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ComponentInsProc >>>'
go


/* 
 * PROCEDURE: ComponentUpdProc 
 */

CREATE PROCEDURE ComponentUpdProc
(
    @ComponentId           int,
    @ComponentSequence     int                    = NULL,
    @ItemId                int                    = NULL,
    @DigitalFormatId       int                    = NULL,
    @HardwareId            int                    = NULL,
    @OperatingSystemId     int                    = NULL,
    @SoftwareId            int                    = NULL,
    @CreatedDate           datetime,
    @CreatedBy             varchar(50),
    @ModifiedDate          datetime               = NULL,
    @ModifiedBy            varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE Component
       SET ComponentSequence      = @ComponentSequence,
           ItemId                 = @ItemId,
           DigitalFormatId        = @DigitalFormatId,
           HardwareId             = @HardwareId,
           OperatingSystemId      = @OperatingSystemId,
           SoftwareId             = @SoftwareId,
           CreatedDate            = @CreatedDate,
           CreatedBy              = @CreatedBy,
           ModifiedDate           = @ModifiedDate,
           ModifiedBy             = @ModifiedBy
     WHERE ComponentId = @ComponentId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'ComponentUpdProc: Cannot update  in Component '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ComponentUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ComponentUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ComponentUpdProc >>>'
go


/* 
 * PROCEDURE: ComponentDelProc 
 */

CREATE PROCEDURE ComponentDelProc
(
    @ComponentId           int)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM Component
     WHERE ComponentId = @ComponentId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'ComponentDelProc: Cannot delete because foreign keys still exist in Component '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ComponentDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ComponentDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ComponentDelProc >>>'
go


/* 
 * PROCEDURE: ComponentSelProc 
 */

CREATE PROCEDURE ComponentSelProc
(
    @ComponentId           int)
AS
BEGIN
    SELECT ComponentId,
           ComponentSequence,
           ItemId,
           DigitalFormatId,
           HardwareId,
           OperatingSystemId,
           SoftwareId,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM Component
     WHERE ComponentId = @ComponentId

    RETURN(0)
END
go
IF OBJECT_ID('ComponentSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ComponentSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ComponentSelProc >>>'
go


/* 
 * PROCEDURE: DigestAlgorithmInsProc 
 */

CREATE PROCEDURE DigestAlgorithmInsProc
(
    @DigestAlgorithmName     varchar(50),
    @CreatedDate             datetime,
    @CreatedBy               varchar(50),
    @ModifiedDate            datetime               = NULL,
    @ModifiedBy              varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO DigestAlgorithm(DigestAlgorithmName,
                                CreatedDate,
                                CreatedBy,
                                ModifiedDate,
                                ModifiedBy)
    VALUES(@DigestAlgorithmName,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'DigestAlgorithmInsProc: Cannot insert because primary key value not found in DigestAlgorithm '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('DigestAlgorithmInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DigestAlgorithmInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DigestAlgorithmInsProc >>>'
go


/* 
 * PROCEDURE: DigestAlgorithmUpdProc 
 */

CREATE PROCEDURE DigestAlgorithmUpdProc
(
    @DigestAlgorithmId       int,
    @DigestAlgorithmName     varchar(50),
    @CreatedDate             datetime,
    @CreatedBy               varchar(50),
    @ModifiedDate            datetime               = NULL,
    @ModifiedBy              varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE DigestAlgorithm
       SET DigestAlgorithmName      = @DigestAlgorithmName,
           CreatedDate              = @CreatedDate,
           CreatedBy                = @CreatedBy,
           ModifiedDate             = @ModifiedDate,
           ModifiedBy               = @ModifiedBy
     WHERE DigestAlgorithmId = @DigestAlgorithmId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'DigestAlgorithmUpdProc: Cannot update  in DigestAlgorithm '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('DigestAlgorithmUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DigestAlgorithmUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DigestAlgorithmUpdProc >>>'
go


/* 
 * PROCEDURE: DigestAlgorithmDelProc 
 */

CREATE PROCEDURE DigestAlgorithmDelProc
(
    @DigestAlgorithmId       int)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM DigestAlgorithm
     WHERE DigestAlgorithmId = @DigestAlgorithmId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'DigestAlgorithmDelProc: Cannot delete because foreign keys still exist in DigestAlgorithm '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('DigestAlgorithmDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DigestAlgorithmDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DigestAlgorithmDelProc >>>'
go


/* 
 * PROCEDURE: DigestAlgorithmSelProc 
 */

CREATE PROCEDURE DigestAlgorithmSelProc
(
    @DigestAlgorithmId       int)
AS
BEGIN
    SELECT DigestAlgorithmId,
           DigestAlgorithmName,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM DigestAlgorithm
     WHERE DigestAlgorithmId = @DigestAlgorithmId

    RETURN(0)
END
go
IF OBJECT_ID('DigestAlgorithmSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DigestAlgorithmSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DigestAlgorithmSelProc >>>'
go


/* 
 * PROCEDURE: DigitalFormatInsProc 
 */

CREATE PROCEDURE DigitalFormatInsProc
(
    @DigitalFormatFileType                     varchar(5),
    @DigitalFormatRenderingSoftwareName        varchar(50)            = NULL,
    @DigitalFormatRenderingSoftwareVersion     varchar(20)            = NULL,
    @DigitalFormatRegistryId                   int                    = NULL,
    @DigitalFormatMIMEType                     varchar(50),
    @CreatedDate                               datetime,
    @CreatedBy                                 varchar(50),
    @ModifiedDate                              datetime               = NULL,
    @ModifiedBy                                varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO DigitalFormat(DigitalFormatFileType,
                              DigitalFormatRenderingSoftwareName,
                              DigitalFormatRenderingSoftwareVersion,
                              DigitalFormatRegistryId,
                              DigitalFormatMIMEType,
                              CreatedDate,
                              CreatedBy,
                              ModifiedDate,
                              ModifiedBy)
    VALUES(@DigitalFormatFileType,
           @DigitalFormatRenderingSoftwareName,
           @DigitalFormatRenderingSoftwareVersion,
           @DigitalFormatRegistryId,
           @DigitalFormatMIMEType,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'DigitalFormatInsProc: Cannot insert because primary key value not found in DigitalFormat '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('DigitalFormatInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DigitalFormatInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DigitalFormatInsProc >>>'
go


/* 
 * PROCEDURE: DigitalFormatUpdProc 
 */

CREATE PROCEDURE DigitalFormatUpdProc
(
    @DigitalFormatId                           int,
    @DigitalFormatFileType                     varchar(5),
    @DigitalFormatRenderingSoftwareName        varchar(50)            = NULL,
    @DigitalFormatRenderingSoftwareVersion     varchar(20)            = NULL,
    @DigitalFormatRegistryId                   int                    = NULL,
    @DigitalFormatMIMEType                     varchar(50),
    @CreatedDate                               datetime,
    @CreatedBy                                 varchar(50),
    @ModifiedDate                              datetime               = NULL,
    @ModifiedBy                                varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE DigitalFormat
       SET DigitalFormatFileType                      = @DigitalFormatFileType,
           DigitalFormatRenderingSoftwareName         = @DigitalFormatRenderingSoftwareName,
           DigitalFormatRenderingSoftwareVersion      = @DigitalFormatRenderingSoftwareVersion,
           DigitalFormatRegistryId                    = @DigitalFormatRegistryId,
           DigitalFormatMIMEType                      = @DigitalFormatMIMEType,
           CreatedDate                                = @CreatedDate,
           CreatedBy                                  = @CreatedBy,
           ModifiedDate                               = @ModifiedDate,
           ModifiedBy                                 = @ModifiedBy
     WHERE DigitalFormatId = @DigitalFormatId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'DigitalFormatUpdProc: Cannot update  in DigitalFormat '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('DigitalFormatUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DigitalFormatUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DigitalFormatUpdProc >>>'
go


/* 
 * PROCEDURE: DigitalFormatDelProc 
 */

CREATE PROCEDURE DigitalFormatDelProc
(
    @DigitalFormatId                           int)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM DigitalFormat
     WHERE DigitalFormatId = @DigitalFormatId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'DigitalFormatDelProc: Cannot delete because foreign keys still exist in DigitalFormat '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('DigitalFormatDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DigitalFormatDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DigitalFormatDelProc >>>'
go


/* 
 * PROCEDURE: DigitalFormatSelProc 
 */

CREATE PROCEDURE DigitalFormatSelProc
(
    @DigitalFormatId                           int)
AS
BEGIN
    SELECT DigitalFormatId,
           DigitalFormatFileType,
           DigitalFormatRenderingSoftwareName,
           DigitalFormatRenderingSoftwareVersion,
           DigitalFormatRegistryId,
           DigitalFormatMIMEType,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM DigitalFormat
     WHERE DigitalFormatId = @DigitalFormatId

    RETURN(0)
END
go
IF OBJECT_ID('DigitalFormatSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DigitalFormatSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DigitalFormatSelProc >>>'
go


/* 
 * PROCEDURE: DigitalFormatRegistryInsProc 
 */

CREATE PROCEDURE DigitalFormatRegistryInsProc
(
    @DigitalFormatRegistryName       varchar(50),
    @DigitalFormatRegistryRoleId     int                    = NULL,
    @CreatedDate                     datetime,
    @CreatedBy                       varchar(50),
    @ModifiedDate                    datetime               = NULL,
    @ModifiedBy                      varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO DigitalFormatRegistry(DigitalFormatRegistryName,
                                      DigitalFormatRegistryRoleId,
                                      CreatedDate,
                                      CreatedBy,
                                      ModifiedDate,
                                      ModifiedBy)
    VALUES(@DigitalFormatRegistryName,
           @DigitalFormatRegistryRoleId,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'DigitalFormatRegistryInsProc: Cannot insert because primary key value not found in DigitalFormatRegistry '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('DigitalFormatRegistryInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DigitalFormatRegistryInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DigitalFormatRegistryInsProc >>>'
go


/* 
 * PROCEDURE: DigitalFormatRegistryUpdProc 
 */

CREATE PROCEDURE DigitalFormatRegistryUpdProc
(
    @DigitalFormatRegistryId         int,
    @DigitalFormatRegistryName       varchar(50),
    @DigitalFormatRegistryRoleId     int                    = NULL,
    @CreatedDate                     datetime,
    @CreatedBy                       varchar(50),
    @ModifiedDate                    datetime               = NULL,
    @ModifiedBy                      varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE DigitalFormatRegistry
       SET DigitalFormatRegistryName        = @DigitalFormatRegistryName,
           DigitalFormatRegistryRoleId      = @DigitalFormatRegistryRoleId,
           CreatedDate                      = @CreatedDate,
           CreatedBy                        = @CreatedBy,
           ModifiedDate                     = @ModifiedDate,
           ModifiedBy                       = @ModifiedBy
     WHERE DigitalFormatRegistryId = @DigitalFormatRegistryId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'DigitalFormatRegistryUpdProc: Cannot update  in DigitalFormatRegistry '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('DigitalFormatRegistryUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DigitalFormatRegistryUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DigitalFormatRegistryUpdProc >>>'
go


/* 
 * PROCEDURE: DigitalFormatRegistryDelProc 
 */

CREATE PROCEDURE DigitalFormatRegistryDelProc
(
    @DigitalFormatRegistryId         int)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM DigitalFormatRegistry
     WHERE DigitalFormatRegistryId = @DigitalFormatRegistryId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'DigitalFormatRegistryDelProc: Cannot delete because foreign keys still exist in DigitalFormatRegistry '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('DigitalFormatRegistryDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DigitalFormatRegistryDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DigitalFormatRegistryDelProc >>>'
go


/* 
 * PROCEDURE: DigitalFormatRegistrySelProc 
 */

CREATE PROCEDURE DigitalFormatRegistrySelProc
(
    @DigitalFormatRegistryId         int)
AS
BEGIN
    SELECT DigitalFormatRegistryId,
           DigitalFormatRegistryName,
           DigitalFormatRegistryRoleId,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM DigitalFormatRegistry
     WHERE DigitalFormatRegistryId = @DigitalFormatRegistryId

    RETURN(0)
END
go
IF OBJECT_ID('DigitalFormatRegistrySelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DigitalFormatRegistrySelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DigitalFormatRegistrySelProc >>>'
go


/* 
 * PROCEDURE: DigitalFormatRegistryRoleInsProc 
 */

CREATE PROCEDURE DigitalFormatRegistryRoleInsProc
(
    @DigitalFormatRegistryRoleName     varchar(50),
    @CreatedDate                       datetime,
    @CreatedBy                         varchar(50),
    @ModifiedDate                      datetime               = NULL,
    @ModifiedBy                        varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO DigitalFormatRegistryRole(DigitalFormatRegistryRoleName,
                                          CreatedDate,
                                          CreatedBy,
                                          ModifiedDate,
                                          ModifiedBy)
    VALUES(@DigitalFormatRegistryRoleName,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'DigitalFormatRegistryRoleInsProc: Cannot insert because primary key value not found in DigitalFormatRegistryRole '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('DigitalFormatRegistryRoleInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DigitalFormatRegistryRoleInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DigitalFormatRegistryRoleInsProc >>>'
go


/* 
 * PROCEDURE: DigitalFormatRegistryRoleUpdProc 
 */

CREATE PROCEDURE DigitalFormatRegistryRoleUpdProc
(
    @DigitalFormatRegistryRoleId       int,
    @DigitalFormatRegistryRoleName     varchar(50),
    @CreatedDate                       datetime,
    @CreatedBy                         varchar(50),
    @ModifiedDate                      datetime               = NULL,
    @ModifiedBy                        varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE DigitalFormatRegistryRole
       SET DigitalFormatRegistryRoleName      = @DigitalFormatRegistryRoleName,
           CreatedDate                        = @CreatedDate,
           CreatedBy                          = @CreatedBy,
           ModifiedDate                       = @ModifiedDate,
           ModifiedBy                         = @ModifiedBy
     WHERE DigitalFormatRegistryRoleId = @DigitalFormatRegistryRoleId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'DigitalFormatRegistryRoleUpdProc: Cannot update  in DigitalFormatRegistryRole '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('DigitalFormatRegistryRoleUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DigitalFormatRegistryRoleUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DigitalFormatRegistryRoleUpdProc >>>'
go


/* 
 * PROCEDURE: DigitalFormatRegistryRoleDelProc 
 */

CREATE PROCEDURE DigitalFormatRegistryRoleDelProc
(
    @DigitalFormatRegistryRoleId       int)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM DigitalFormatRegistryRole
     WHERE DigitalFormatRegistryRoleId = @DigitalFormatRegistryRoleId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'DigitalFormatRegistryRoleDelProc: Cannot delete because foreign keys still exist in DigitalFormatRegistryRole '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('DigitalFormatRegistryRoleDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DigitalFormatRegistryRoleDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DigitalFormatRegistryRoleDelProc >>>'
go


/* 
 * PROCEDURE: DigitalFormatRegistryRoleSelProc 
 */

CREATE PROCEDURE DigitalFormatRegistryRoleSelProc
(
    @DigitalFormatRegistryRoleId       int)
AS
BEGIN
    SELECT DigitalFormatRegistryRoleId,
           DigitalFormatRegistryRoleName,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM DigitalFormatRegistryRole
     WHERE DigitalFormatRegistryRoleId = @DigitalFormatRegistryRoleId

    RETURN(0)
END
go
IF OBJECT_ID('DigitalFormatRegistryRoleSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DigitalFormatRegistryRoleSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DigitalFormatRegistryRoleSelProc >>>'
go


/* 
 * PROCEDURE: DigitalSignatureKeyInsProc 
 */

CREATE PROCEDURE DigitalSignatureKeyInsProc
(
    @DigitalSignatureKeyType             varchar(50),
    @DigitalSignatureKeyValue            varchar(100)            = NULL,
    @DigitalSignatureKeyInformation      varchar(max)            = NULL,
    @DigitalSignatureKeyVerification     varchar(max)            = NULL,
    @CreatedDate                         datetime,
    @CreatedBy                           varchar(50),
    @ModifiedDate                        datetime                = NULL,
    @ModifiedBy                          varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO DigitalSignatureKey(DigitalSignatureKeyType,
                                    DigitalSignatureKeyValue,
                                    DigitalSignatureKeyInformation,
                                    DigitalSignatureKeyVerification,
                                    CreatedDate,
                                    CreatedBy,
                                    ModifiedDate,
                                    ModifiedBy)
    VALUES(@DigitalSignatureKeyType,
           @DigitalSignatureKeyValue,
           @DigitalSignatureKeyInformation,
           @DigitalSignatureKeyVerification,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'DigitalSignatureKeyInsProc: Cannot insert because primary key value not found in DigitalSignatureKey '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('DigitalSignatureKeyInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DigitalSignatureKeyInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DigitalSignatureKeyInsProc >>>'
go


/* 
 * PROCEDURE: DigitalSignatureKeyUpdProc 
 */

CREATE PROCEDURE DigitalSignatureKeyUpdProc
(
    @DigitalSignatureKeyId               int,
    @DigitalSignatureKeyType             varchar(50),
    @DigitalSignatureKeyValue            varchar(100)            = NULL,
    @DigitalSignatureKeyInformation      varchar(max)            = NULL,
    @DigitalSignatureKeyVerification     varchar(max)            = NULL,
    @CreatedDate                         datetime,
    @CreatedBy                           varchar(50),
    @ModifiedDate                        datetime                = NULL,
    @ModifiedBy                          varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE DigitalSignatureKey
       SET DigitalSignatureKeyType              = @DigitalSignatureKeyType,
           DigitalSignatureKeyValue             = @DigitalSignatureKeyValue,
           DigitalSignatureKeyInformation       = @DigitalSignatureKeyInformation,
           DigitalSignatureKeyVerification      = @DigitalSignatureKeyVerification,
           CreatedDate                          = @CreatedDate,
           CreatedBy                            = @CreatedBy,
           ModifiedDate                         = @ModifiedDate,
           ModifiedBy                           = @ModifiedBy
     WHERE DigitalSignatureKeyId = @DigitalSignatureKeyId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'DigitalSignatureKeyUpdProc: Cannot update  in DigitalSignatureKey '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('DigitalSignatureKeyUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DigitalSignatureKeyUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DigitalSignatureKeyUpdProc >>>'
go


/* 
 * PROCEDURE: DigitalSignatureKeyDelProc 
 */

CREATE PROCEDURE DigitalSignatureKeyDelProc
(
    @DigitalSignatureKeyId               int)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM DigitalSignatureKey
     WHERE DigitalSignatureKeyId = @DigitalSignatureKeyId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'DigitalSignatureKeyDelProc: Cannot delete because foreign keys still exist in DigitalSignatureKey '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('DigitalSignatureKeyDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DigitalSignatureKeyDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DigitalSignatureKeyDelProc >>>'
go


/* 
 * PROCEDURE: DigitalSignatureKeySelProc 
 */

CREATE PROCEDURE DigitalSignatureKeySelProc
(
    @DigitalSignatureKeyId               int)
AS
BEGIN
    SELECT DigitalSignatureKeyId,
           DigitalSignatureKeyType,
           DigitalSignatureKeyValue,
           DigitalSignatureKeyInformation,
           DigitalSignatureKeyVerification,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM DigitalSignatureKey
     WHERE DigitalSignatureKeyId = @DigitalSignatureKeyId

    RETURN(0)
END
go
IF OBJECT_ID('DigitalSignatureKeySelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE DigitalSignatureKeySelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE DigitalSignatureKeySelProc >>>'
go


/* 
 * PROCEDURE: FilingUnitInsProc 
 */

CREATE PROCEDURE FilingUnitInsProc
(
    @FilingUnitName     varchar(255),
    @CreatedDate        datetime,
    @CreatedBy          varchar(50),
    @ModifiedDate       datetime                = NULL,
    @ModifiedBy         varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO FilingUnit(FilingUnitName,
                           CreatedDate,
                           CreatedBy,
                           ModifiedDate,
                           ModifiedBy)
    VALUES(@FilingUnitName,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'FilingUnitInsProc: Cannot insert because primary key value not found in FilingUnit '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('FilingUnitInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE FilingUnitInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE FilingUnitInsProc >>>'
go


/* 
 * PROCEDURE: FilingUnitUpdProc 
 */

CREATE PROCEDURE FilingUnitUpdProc
(
    @FilingUnitId       int,
    @FilingUnitName     varchar(255),
    @CreatedDate        datetime,
    @CreatedBy          varchar(50),
    @ModifiedDate       datetime                = NULL,
    @ModifiedBy         varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE FilingUnit
       SET FilingUnitName      = @FilingUnitName,
           CreatedDate         = @CreatedDate,
           CreatedBy           = @CreatedBy,
           ModifiedDate        = @ModifiedDate,
           ModifiedBy          = @ModifiedBy
     WHERE FilingUnitId = @FilingUnitId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'FilingUnitUpdProc: Cannot update  in FilingUnit '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('FilingUnitUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE FilingUnitUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE FilingUnitUpdProc >>>'
go


/* 
 * PROCEDURE: FilingUnitDelProc 
 */

CREATE PROCEDURE FilingUnitDelProc
(
    @FilingUnitId       int)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM FilingUnit
     WHERE FilingUnitId = @FilingUnitId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'FilingUnitDelProc: Cannot delete because foreign keys still exist in FilingUnit '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('FilingUnitDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE FilingUnitDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE FilingUnitDelProc >>>'
go


/* 
 * PROCEDURE: FilingUnitSelProc 
 */

CREATE PROCEDURE FilingUnitSelProc
(
    @FilingUnitId       int)
AS
BEGIN
    SELECT FilingUnitId,
           FilingUnitName,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM FilingUnit
     WHERE FilingUnitId = @FilingUnitId

    RETURN(0)
END
go
IF OBJECT_ID('FilingUnitSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE FilingUnitSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE FilingUnitSelProc >>>'
go


/* 
 * PROCEDURE: HardwareInsProc 
 */

CREATE PROCEDURE HardwareInsProc
(
    @HardwareName              varchar(50),
    @HardwareType              char(10)                = NULL,
    @HardwareLocation          varchar(50)             = NULL,
    @HardwareDocumentation     varchar(100)            = NULL,
    @CreatedDate               datetime,
    @CreatedBy                 varchar(50),
    @ModifiedDate              datetime                = NULL,
    @ModifiedBy                varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO Hardware(HardwareName,
                         HardwareType,
                         HardwareLocation,
                         HardwareDocumentation,
                         CreatedDate,
                         CreatedBy,
                         ModifiedDate,
                         ModifiedBy)
    VALUES(@HardwareName,
           @HardwareType,
           @HardwareLocation,
           @HardwareDocumentation,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'HardwareInsProc: Cannot insert because primary key value not found in Hardware '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('HardwareInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE HardwareInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE HardwareInsProc >>>'
go


/* 
 * PROCEDURE: HardwareUpdProc 
 */

CREATE PROCEDURE HardwareUpdProc
(
    @HardwareId                int,
    @HardwareName              varchar(50),
    @HardwareType              char(10)                = NULL,
    @HardwareLocation          varchar(50)             = NULL,
    @HardwareDocumentation     varchar(100)            = NULL,
    @CreatedDate               datetime,
    @CreatedBy                 varchar(50),
    @ModifiedDate              datetime                = NULL,
    @ModifiedBy                varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE Hardware
       SET HardwareName               = @HardwareName,
           HardwareType               = @HardwareType,
           HardwareLocation           = @HardwareLocation,
           HardwareDocumentation      = @HardwareDocumentation,
           CreatedDate                = @CreatedDate,
           CreatedBy                  = @CreatedBy,
           ModifiedDate               = @ModifiedDate,
           ModifiedBy                 = @ModifiedBy
     WHERE HardwareId = @HardwareId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'HardwareUpdProc: Cannot update  in Hardware '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('HardwareUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE HardwareUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE HardwareUpdProc >>>'
go


/* 
 * PROCEDURE: HardwareDelProc 
 */

CREATE PROCEDURE HardwareDelProc
(
    @HardwareId                int)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM Hardware
     WHERE HardwareId = @HardwareId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'HardwareDelProc: Cannot delete because foreign keys still exist in Hardware '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('HardwareDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE HardwareDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE HardwareDelProc >>>'
go


/* 
 * PROCEDURE: HardwareSelProc 
 */

CREATE PROCEDURE HardwareSelProc
(
    @HardwareId                int)
AS
BEGIN
    SELECT HardwareId,
           HardwareName,
           HardwareType,
           HardwareLocation,
           HardwareDocumentation,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM Hardware
     WHERE HardwareId = @HardwareId

    RETURN(0)
END
go
IF OBJECT_ID('HardwareSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE HardwareSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE HardwareSelProc >>>'
go


/* 
 * PROCEDURE: ItemInsProc 
 */

CREATE PROCEDURE ItemInsProc
(
    @ItemId                          int,
    @SeriesId                        int,
    @ItemTitle                       varchar(250),
    @ItemDisplayTitle                varchar(250)             = NULL,
    @ItemDescription                 varchar(max)             = NULL,
    @ItemExtentDescription           varchar(max)             = NULL,
    @ItemOpenDate                    datetime                 = NULL,
    @ItemSample                      varchar(max)             = NULL,
    @ItemReceivedFileName            varchar(250)             = NULL,
    @ItemOriginatorIdentifier        varchar(50)              = NULL,
    @ItemLanguage                    char(3)                  = NULL,
    @ItemAccessFacilitators          varchar(1250)            = NULL,
    @ItemExceptions                  varchar(1250)            = NULL,
    @HardwareId                      int                      = NULL,
    @OperatingSystemId               int                      = NULL,
    @SoftwareId                      int                      = NULL,
    @DigestAlgorithmId               int                      = NULL,
    @ItemReceivedHashOriginator      varchar(50)              = NULL,
    @ItemReceivedHashDate            datetime                 = NULL,
    @ItemReceivedHashValue           varchar(256)             = NULL,
    @ItemTechnicalInfrastructure     varchar(1250)            = NULL,
    @ItemGeneralNote                 varchar(max)             = NULL,
    @DigitalFormatId                 int                      = NULL,
    @FilingUnitId                    int                      = NULL,
    @SuperPackageId                  varchar(13)              = NULL,
    @CreatedDate                     datetime,
    @CreatedBy                       varchar(50),
    @ModifiedDate                    datetime                 = NULL,
    @ModifiedBy                      varchar(50)              = NULL,
    @ItemGeoLocationId               int                      = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO Item(ItemId,
                     SeriesId,
                     ItemTitle,
                     ItemDisplayTitle,
                     ItemDescription,
                     ItemExtentDescription,
                     ItemOpenDate,
                     ItemSample,
                     ItemReceivedFileName,
                     ItemOriginatorIdentifier,
                     ItemLanguage,
                     ItemAccessFacilitators,
                     ItemExceptions,
                     HardwareId,
                     OperatingSystemId,
                     SoftwareId,
                     DigestAlgorithmId,
                     ItemReceivedHashOriginator,
                     ItemReceivedHashDate,
                     ItemReceivedHashValue,
                     ItemTechnicalInfrastructure,
                     ItemGeneralNote,
                     DigitalFormatId,
                     FilingUnitId,
                     SuperPackageId,
                     CreatedDate,
                     CreatedBy,
                     ModifiedDate,
                     ModifiedBy,
                     ItemGeoLocationId)
    VALUES(@ItemId,
           @SeriesId,
           @ItemTitle,
           @ItemDisplayTitle,
           @ItemDescription,
           @ItemExtentDescription,
           @ItemOpenDate,
           @ItemSample,
           @ItemReceivedFileName,
           @ItemOriginatorIdentifier,
           @ItemLanguage,
           @ItemAccessFacilitators,
           @ItemExceptions,
           @HardwareId,
           @OperatingSystemId,
           @SoftwareId,
           @DigestAlgorithmId,
           @ItemReceivedHashOriginator,
           @ItemReceivedHashDate,
           @ItemReceivedHashValue,
           @ItemTechnicalInfrastructure,
           @ItemGeneralNote,
           @DigitalFormatId,
           @FilingUnitId,
           @SuperPackageId,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy,
           @ItemGeoLocationId)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'ItemInsProc: Cannot insert because primary key value not found in Item '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('ItemInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemInsProc >>>'
go


/* 
 * PROCEDURE: ItemUpdProc 
 */

CREATE PROCEDURE ItemUpdProc
(
    @ItemId                          int,
    @SeriesId                        int,
    @ItemTitle                       varchar(250),
    @ItemDisplayTitle                varchar(250)             = NULL,
    @ItemDescription                 varchar(max)             = NULL,
    @ItemExtentDescription           varchar(max)             = NULL,
    @ItemOpenDate                    datetime                 = NULL,
    @ItemSample                      varchar(max)             = NULL,
    @ItemReceivedFileName            varchar(250)             = NULL,
    @ItemOriginatorIdentifier        varchar(50)              = NULL,
    @ItemLanguage                    char(3)                  = NULL,
    @ItemAccessFacilitators          varchar(1250)            = NULL,
    @ItemExceptions                  varchar(1250)            = NULL,
    @HardwareId                      int                      = NULL,
    @OperatingSystemId               int                      = NULL,
    @SoftwareId                      int                      = NULL,
    @DigestAlgorithmId               int                      = NULL,
    @ItemReceivedHashOriginator      varchar(50)              = NULL,
    @ItemReceivedHashDate            datetime                 = NULL,
    @ItemReceivedHashValue           varchar(256)             = NULL,
    @ItemTechnicalInfrastructure     varchar(1250)            = NULL,
    @ItemGeneralNote                 varchar(max)             = NULL,
    @DigitalFormatId                 int                      = NULL,
    @FilingUnitId                    int                      = NULL,
    @SuperPackageId                  varchar(13)              = NULL,
    @CreatedDate                     datetime,
    @CreatedBy                       varchar(50),
    @ModifiedDate                    datetime                 = NULL,
    @ModifiedBy                      varchar(50)              = NULL,
    @ItemGeoLocationId               int                      = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE Item
       SET SeriesId                         = @SeriesId,
           ItemTitle                        = @ItemTitle,
           ItemDisplayTitle                 = @ItemDisplayTitle,
           ItemDescription                  = @ItemDescription,
           ItemExtentDescription            = @ItemExtentDescription,
           ItemOpenDate                     = @ItemOpenDate,
           ItemSample                       = @ItemSample,
           ItemReceivedFileName             = @ItemReceivedFileName,
           ItemOriginatorIdentifier         = @ItemOriginatorIdentifier,
           ItemLanguage                     = @ItemLanguage,
           ItemAccessFacilitators           = @ItemAccessFacilitators,
           ItemExceptions                   = @ItemExceptions,
           HardwareId                       = @HardwareId,
           OperatingSystemId                = @OperatingSystemId,
           SoftwareId                       = @SoftwareId,
           DigestAlgorithmId                = @DigestAlgorithmId,
           ItemReceivedHashOriginator       = @ItemReceivedHashOriginator,
           ItemReceivedHashDate             = @ItemReceivedHashDate,
           ItemReceivedHashValue            = @ItemReceivedHashValue,
           ItemTechnicalInfrastructure      = @ItemTechnicalInfrastructure,
           ItemGeneralNote                  = @ItemGeneralNote,
           DigitalFormatId                  = @DigitalFormatId,
           FilingUnitId                     = @FilingUnitId,
           SuperPackageId                   = @SuperPackageId,
           CreatedDate                      = @CreatedDate,
           CreatedBy                        = @CreatedBy,
           ModifiedDate                     = @ModifiedDate,
           ModifiedBy                       = @ModifiedBy,
           ItemGeoLocationId                = @ItemGeoLocationId
     WHERE ItemId = @ItemId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'ItemUpdProc: Cannot update  in Item '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ItemUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemUpdProc >>>'
go


/* 
 * PROCEDURE: ItemDelProc 
 */

CREATE PROCEDURE ItemDelProc
(
    @ItemId                          int)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM Item
     WHERE ItemId = @ItemId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'ItemDelProc: Cannot delete because foreign keys still exist in Item '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ItemDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemDelProc >>>'
go


/* 
 * PROCEDURE: ItemSelProc 
 */

CREATE PROCEDURE ItemSelProc
(
    @ItemId                          int)
AS
BEGIN
    SELECT ItemId,
           SeriesId,
           ItemTitle,
           ItemDisplayTitle,
           ItemDescription,
           ItemExtentDescription,
           ItemOpenDate,
           ItemSample,
           ItemReceivedFileName,
           ItemOriginatorIdentifier,
           ItemLanguage,
           ItemAccessFacilitators,
           ItemExceptions,
           HardwareId,
           OperatingSystemId,
           SoftwareId,
           DigestAlgorithmId,
           ItemReceivedHashOriginator,
           ItemReceivedHashDate,
           ItemReceivedHashValue,
           ItemTechnicalInfrastructure,
           ItemGeneralNote,
           DigitalFormatId,
           FilingUnitId,
           SuperPackageId,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy,
           ItemGeoLocationId
      FROM Item
     WHERE ItemId = @ItemId

    RETURN(0)
END
go
IF OBJECT_ID('ItemSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemSelProc >>>'
go


/* 
 * PROCEDURE: ItemDateInsProc 
 */

CREATE PROCEDURE ItemDateInsProc
(
    @ItemId            int,
    @QualifierId       int,
    @ItemDateValue     varchar(35),
    @CreatedDate       datetime,
    @CreatedBy         varchar(50),
    @ModifiedDate      datetime               = NULL,
    @ModifiedBy        varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO ItemDate(ItemId,
                         QualifierId,
                         ItemDateValue,
                         CreatedDate,
                         CreatedBy,
                         ModifiedDate,
                         ModifiedBy)
    VALUES(@ItemId,
           @QualifierId,
           @ItemDateValue,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'ItemDateInsProc: Cannot insert because primary key value not found in ItemDate '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('ItemDateInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemDateInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemDateInsProc >>>'
go


/* 
 * PROCEDURE: ItemDateUpdProc 
 */

CREATE PROCEDURE ItemDateUpdProc
(
    @ItemId            int,
    @QualifierId       int,
    @ItemDateValue     varchar(35),
    @CreatedDate       datetime,
    @CreatedBy         varchar(50),
    @ModifiedDate      datetime               = NULL,
    @ModifiedBy        varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE ItemDate
       SET ItemId             = @ItemId,
           QualifierId        = @QualifierId,
           CreatedDate        = @CreatedDate,
           CreatedBy          = @CreatedBy,
           ModifiedDate       = @ModifiedDate,
           ModifiedBy         = @ModifiedBy
     WHERE ItemDateValue = @ItemDateValue

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'ItemDateUpdProc: Cannot update  in ItemDate '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ItemDateUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemDateUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemDateUpdProc >>>'
go


/* 
 * PROCEDURE: ItemDateDelProc 
 */

CREATE PROCEDURE ItemDateDelProc
(
    @ItemDateValue     varchar(35))
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM ItemDate
     WHERE ItemDateValue = @ItemDateValue

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'ItemDateDelProc: Cannot delete because foreign keys still exist in ItemDate '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ItemDateDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemDateDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemDateDelProc >>>'
go


/* 
 * PROCEDURE: ItemDateSelProc 
 */

CREATE PROCEDURE ItemDateSelProc
(
    @ItemDateValue     varchar(35))
AS
BEGIN
    SELECT ItemId,
           QualifierId,
           ItemDateValue,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM ItemDate
     WHERE ItemDateValue = @ItemDateValue

    RETURN(0)
END
go
IF OBJECT_ID('ItemDateSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemDateSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemDateSelProc >>>'
go


/* 
 * PROCEDURE: ItemKeywordInsProc 
 */

CREATE PROCEDURE ItemKeywordInsProc
(
    @ItemId           int,
    @Keyword          varchar(250),
    @CreatedDate      datetime,
    @CreatedBy        varchar(50),
    @ModifiedDate     datetime                = NULL,
    @ModifiedBy       varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO ItemKeyword(ItemId,
                            Keyword,
                            CreatedDate,
                            CreatedBy,
                            ModifiedDate,
                            ModifiedBy)
    VALUES(@ItemId,
           @Keyword,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'ItemKeywordInsProc: Cannot insert because primary key value not found in ItemKeyword '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('ItemKeywordInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemKeywordInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemKeywordInsProc >>>'
go


/* 
 * PROCEDURE: ItemKeywordUpdProc 
 */

CREATE PROCEDURE ItemKeywordUpdProc
(
    @ItemId           int,
    @Keyword          varchar(250),
    @CreatedDate      datetime,
    @CreatedBy        varchar(50),
    @ModifiedDate     datetime                = NULL,
    @ModifiedBy       varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE ItemKeyword
       SET ItemId            = @ItemId,
           CreatedDate       = @CreatedDate,
           CreatedBy         = @CreatedBy,
           ModifiedDate      = @ModifiedDate,
           ModifiedBy        = @ModifiedBy
     WHERE Keyword = @Keyword

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'ItemKeywordUpdProc: Cannot update  in ItemKeyword '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ItemKeywordUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemKeywordUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemKeywordUpdProc >>>'
go


/* 
 * PROCEDURE: ItemKeywordDelProc 
 */

CREATE PROCEDURE ItemKeywordDelProc
(
    @Keyword          varchar(250))
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM ItemKeyword
     WHERE Keyword = @Keyword

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'ItemKeywordDelProc: Cannot delete because foreign keys still exist in ItemKeyword '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ItemKeywordDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemKeywordDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemKeywordDelProc >>>'
go


/* 
 * PROCEDURE: ItemKeywordSelProc 
 */

CREATE PROCEDURE ItemKeywordSelProc
(
    @Keyword          varchar(250))
AS
BEGIN
    SELECT ItemId,
           Keyword,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM ItemKeyword
     WHERE Keyword = @Keyword

    RETURN(0)
END
go
IF OBJECT_ID('ItemKeywordSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemKeywordSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemKeywordSelProc >>>'
go


/* 
 * PROCEDURE: ItemLocationInsProc 
 */

CREATE PROCEDURE ItemLocationInsProc
(
    @ItemLocationId           int,
    @ItemGeoLocationValue     varchar(250),
    @CreatedDate              datetime,
    @CreatedBy                varchar(50),
    @ModifiedDate             datetime                = NULL,
    @ModifiedBy               varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO ItemLocation(ItemLocationId,
                             ItemGeoLocationValue,
                             CreatedDate,
                             CreatedBy,
                             ModifiedDate,
                             ModifiedBy)
    VALUES(@ItemLocationId,
           @ItemGeoLocationValue,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'ItemLocationInsProc: Cannot insert because primary key value not found in ItemLocation '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('ItemLocationInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemLocationInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemLocationInsProc >>>'
go


/* 
 * PROCEDURE: ItemLocationUpdProc 
 */

CREATE PROCEDURE ItemLocationUpdProc
(
    @ItemLocationId           int,
    @ItemGeoLocationValue     varchar(250),
    @CreatedDate              datetime,
    @CreatedBy                varchar(50),
    @ModifiedDate             datetime                = NULL,
    @ModifiedBy               varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE ItemLocation
       SET ItemGeoLocationValue      = @ItemGeoLocationValue,
           CreatedDate               = @CreatedDate,
           CreatedBy                 = @CreatedBy,
           ModifiedDate              = @ModifiedDate,
           ModifiedBy                = @ModifiedBy
     WHERE ItemLocationId = @ItemLocationId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'ItemLocationUpdProc: Cannot update  in ItemLocation '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ItemLocationUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemLocationUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemLocationUpdProc >>>'
go


/* 
 * PROCEDURE: ItemLocationDelProc 
 */

CREATE PROCEDURE ItemLocationDelProc
(
    @ItemLocationId           int)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM ItemLocation
     WHERE ItemLocationId = @ItemLocationId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'ItemLocationDelProc: Cannot delete because foreign keys still exist in ItemLocation '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ItemLocationDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemLocationDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemLocationDelProc >>>'
go


/* 
 * PROCEDURE: ItemLocationSelProc 
 */

CREATE PROCEDURE ItemLocationSelProc
(
    @ItemLocationId           int)
AS
BEGIN
    SELECT ItemLocationId,
           ItemGeoLocationValue,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM ItemLocation
     WHERE ItemLocationId = @ItemLocationId

    RETURN(0)
END
go
IF OBJECT_ID('ItemLocationSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemLocationSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemLocationSelProc >>>'
go


/* 
 * PROCEDURE: ItemOtherIdentifierInsProc 
 */

CREATE PROCEDURE ItemOtherIdentifierInsProc
(
    @ItemId                      int,
    @QualifierId                 int,
    @ItemOtherIdentiferValue     varchar(50),
    @CreatedDate                 datetime,
    @CreatedBy                   varchar(50),
    @ModifiedDate                datetime               = NULL,
    @ModifiedBy                  varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO ItemOtherIdentifier(ItemId,
                                    QualifierId,
                                    ItemOtherIdentiferValue,
                                    CreatedDate,
                                    CreatedBy,
                                    ModifiedDate,
                                    ModifiedBy)
    VALUES(@ItemId,
           @QualifierId,
           @ItemOtherIdentiferValue,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'ItemOtherIdentifierInsProc: Cannot insert because primary key value not found in ItemOtherIdentifier '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('ItemOtherIdentifierInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemOtherIdentifierInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemOtherIdentifierInsProc >>>'
go


/* 
 * PROCEDURE: ItemOtherIdentifierUpdProc 
 */

CREATE PROCEDURE ItemOtherIdentifierUpdProc
(
    @ItemId                      int,
    @QualifierId                 int,
    @ItemOtherIdentiferValue     varchar(50),
    @CreatedDate                 datetime,
    @CreatedBy                   varchar(50),
    @ModifiedDate                datetime               = NULL,
    @ModifiedBy                  varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE ItemOtherIdentifier
       SET ItemId                       = @ItemId,
           QualifierId                  = @QualifierId,
           CreatedDate                  = @CreatedDate,
           CreatedBy                    = @CreatedBy,
           ModifiedDate                 = @ModifiedDate,
           ModifiedBy                   = @ModifiedBy
     WHERE ItemOtherIdentiferValue = @ItemOtherIdentiferValue

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'ItemOtherIdentifierUpdProc: Cannot update  in ItemOtherIdentifier '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ItemOtherIdentifierUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemOtherIdentifierUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemOtherIdentifierUpdProc >>>'
go


/* 
 * PROCEDURE: ItemOtherIdentifierDelProc 
 */

CREATE PROCEDURE ItemOtherIdentifierDelProc
(
    @ItemOtherIdentiferValue     varchar(50))
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM ItemOtherIdentifier
     WHERE ItemOtherIdentiferValue = @ItemOtherIdentiferValue

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'ItemOtherIdentifierDelProc: Cannot delete because foreign keys still exist in ItemOtherIdentifier '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ItemOtherIdentifierDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemOtherIdentifierDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemOtherIdentifierDelProc >>>'
go


/* 
 * PROCEDURE: ItemOtherIdentifierSelProc 
 */

CREATE PROCEDURE ItemOtherIdentifierSelProc
(
    @ItemOtherIdentiferValue     varchar(50))
AS
BEGIN
    SELECT ItemId,
           QualifierId,
           ItemOtherIdentiferValue,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM ItemOtherIdentifier
     WHERE ItemOtherIdentiferValue = @ItemOtherIdentiferValue

    RETURN(0)
END
go
IF OBJECT_ID('ItemOtherIdentifierSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemOtherIdentifierSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemOtherIdentifierSelProc >>>'
go


/* 
 * PROCEDURE: ItemPartyInsProc 
 */

CREATE PROCEDURE ItemPartyInsProc
(
    @ItemId             int,
    @QualifierId        int,
    @ItemPartyValue     varchar(100),
    @CreatedDate        datetime,
    @CreatedBy          varchar(50),
    @ModifiedDate       datetime                = NULL,
    @ModifiedBy         varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO ItemParty(ItemId,
                          QualifierId,
                          ItemPartyValue,
                          CreatedDate,
                          CreatedBy,
                          ModifiedDate,
                          ModifiedBy)
    VALUES(@ItemId,
           @QualifierId,
           @ItemPartyValue,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'ItemPartyInsProc: Cannot insert because primary key value not found in ItemParty '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('ItemPartyInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemPartyInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemPartyInsProc >>>'
go


/* 
 * PROCEDURE: ItemPartyUpdProc 
 */

CREATE PROCEDURE ItemPartyUpdProc
(
    @ItemId             int,
    @QualifierId        int,
    @ItemPartyValue     varchar(100),
    @CreatedDate        datetime,
    @CreatedBy          varchar(50),
    @ModifiedDate       datetime                = NULL,
    @ModifiedBy         varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE ItemParty
       SET ItemId              = @ItemId,
           QualifierId         = @QualifierId,
           CreatedDate         = @CreatedDate,
           CreatedBy           = @CreatedBy,
           ModifiedDate        = @ModifiedDate,
           ModifiedBy          = @ModifiedBy
     WHERE ItemPartyValue = @ItemPartyValue

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'ItemPartyUpdProc: Cannot update  in ItemParty '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ItemPartyUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemPartyUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemPartyUpdProc >>>'
go


/* 
 * PROCEDURE: ItemPartyDelProc 
 */

CREATE PROCEDURE ItemPartyDelProc
(
    @ItemPartyValue     varchar(100))
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM ItemParty
     WHERE ItemPartyValue = @ItemPartyValue

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'ItemPartyDelProc: Cannot delete because foreign keys still exist in ItemParty '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ItemPartyDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemPartyDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemPartyDelProc >>>'
go


/* 
 * PROCEDURE: ItemPartySelProc 
 */

CREATE PROCEDURE ItemPartySelProc
(
    @ItemPartyValue     varchar(100))
AS
BEGIN
    SELECT ItemId,
           QualifierId,
           ItemPartyValue,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM ItemParty
     WHERE ItemPartyValue = @ItemPartyValue

    RETURN(0)
END
go
IF OBJECT_ID('ItemPartySelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemPartySelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemPartySelProc >>>'
go


/* 
 * PROCEDURE: ItemSignatureInsProc 
 */

CREATE PROCEDURE ItemSignatureInsProc
(
    @ItemId                               int,
    @ItemSignatureValue                   varchar(50),
    @ItemSignatureSigner                  char(10)               = NULL,
    @ItemSignatureInformationEncoding     char(10)               = NULL,
    @ItemSignatureMethod                  char(10)               = NULL,
    @ItemSignatureProperties              char(10)               = NULL,
    @DigitalSignatureKeyId                int                    = NULL,
    @CreatedDate                          datetime,
    @CreatedBy                            varchar(50),
    @ModifiedDate                         datetime               = NULL,
    @ModifiedBy                           varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO ItemSignature(ItemId,
                              ItemSignatureValue,
                              ItemSignatureSigner,
                              ItemSignatureInformationEncoding,
                              ItemSignatureMethod,
                              ItemSignatureProperties,
                              DigitalSignatureKeyId,
                              CreatedDate,
                              CreatedBy,
                              ModifiedDate,
                              ModifiedBy)
    VALUES(@ItemId,
           @ItemSignatureValue,
           @ItemSignatureSigner,
           @ItemSignatureInformationEncoding,
           @ItemSignatureMethod,
           @ItemSignatureProperties,
           @DigitalSignatureKeyId,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'ItemSignatureInsProc: Cannot insert because primary key value not found in ItemSignature '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('ItemSignatureInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemSignatureInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemSignatureInsProc >>>'
go


/* 
 * PROCEDURE: ItemSignatureUpdProc 
 */

CREATE PROCEDURE ItemSignatureUpdProc
(
    @ItemId                               int,
    @ItemSignatureValue                   varchar(50),
    @ItemSignatureSigner                  char(10)               = NULL,
    @ItemSignatureInformationEncoding     char(10)               = NULL,
    @ItemSignatureMethod                  char(10)               = NULL,
    @ItemSignatureProperties              char(10)               = NULL,
    @DigitalSignatureKeyId                int                    = NULL,
    @CreatedDate                          datetime,
    @CreatedBy                            varchar(50),
    @ModifiedDate                         datetime               = NULL,
    @ModifiedBy                           varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE ItemSignature
       SET ItemSignatureSigner                   = @ItemSignatureSigner,
           ItemSignatureInformationEncoding      = @ItemSignatureInformationEncoding,
           ItemSignatureMethod                   = @ItemSignatureMethod,
           ItemSignatureProperties               = @ItemSignatureProperties,
           DigitalSignatureKeyId                 = @DigitalSignatureKeyId,
           CreatedDate                           = @CreatedDate,
           CreatedBy                             = @CreatedBy,
           ModifiedDate                          = @ModifiedDate,
           ModifiedBy                            = @ModifiedBy
     WHERE ItemId             = @ItemId
       AND ItemSignatureValue = @ItemSignatureValue

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'ItemSignatureUpdProc: Cannot update  in ItemSignature '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ItemSignatureUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemSignatureUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemSignatureUpdProc >>>'
go


/* 
 * PROCEDURE: ItemSignatureDelProc 
 */

CREATE PROCEDURE ItemSignatureDelProc
(
    @ItemId                               int,
    @ItemSignatureValue                   varchar(50))
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM ItemSignature
     WHERE ItemId             = @ItemId
       AND ItemSignatureValue = @ItemSignatureValue

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'ItemSignatureDelProc: Cannot delete because foreign keys still exist in ItemSignature '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ItemSignatureDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemSignatureDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemSignatureDelProc >>>'
go


/* 
 * PROCEDURE: ItemSignatureSelProc 
 */

CREATE PROCEDURE ItemSignatureSelProc
(
    @ItemId                               int,
    @ItemSignatureValue                   varchar(50))
AS
BEGIN
    SELECT ItemId,
           ItemSignatureValue,
           ItemSignatureSigner,
           ItemSignatureInformationEncoding,
           ItemSignatureMethod,
           ItemSignatureProperties,
           DigitalSignatureKeyId,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM ItemSignature
     WHERE ItemId             = @ItemId
       AND ItemSignatureValue = @ItemSignatureValue

    RETURN(0)
END
go
IF OBJECT_ID('ItemSignatureSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemSignatureSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemSignatureSelProc >>>'
go


/* 
 * PROCEDURE: ItemVariantTitleInsProc 
 */

CREATE PROCEDURE ItemVariantTitleInsProc
(
    @ItemId               int,
    @ItemVariantTitle     varchar(250),
    @CreatedDate          datetime,
    @CreatedBy            varchar(50),
    @ModifiedDate         datetime                = NULL,
    @ModifiedBy           varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO ItemVariantTitle(ItemId,
                                 ItemVariantTitle,
                                 CreatedDate,
                                 CreatedBy,
                                 ModifiedDate,
                                 ModifiedBy)
    VALUES(@ItemId,
           @ItemVariantTitle,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'ItemVariantTitleInsProc: Cannot insert because primary key value not found in ItemVariantTitle '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('ItemVariantTitleInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemVariantTitleInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemVariantTitleInsProc >>>'
go


/* 
 * PROCEDURE: ItemVariantTitleUpdProc 
 */

CREATE PROCEDURE ItemVariantTitleUpdProc
(
    @ItemId               int,
    @ItemVariantTitle     varchar(250),
    @CreatedDate          datetime,
    @CreatedBy            varchar(50),
    @ModifiedDate         datetime                = NULL,
    @ModifiedBy           varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE ItemVariantTitle
       SET ItemId                = @ItemId,
           CreatedDate           = @CreatedDate,
           CreatedBy             = @CreatedBy,
           ModifiedDate          = @ModifiedDate,
           ModifiedBy            = @ModifiedBy
     WHERE ItemVariantTitle = @ItemVariantTitle

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'ItemVariantTitleUpdProc: Cannot update  in ItemVariantTitle '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ItemVariantTitleUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemVariantTitleUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemVariantTitleUpdProc >>>'
go


/* 
 * PROCEDURE: ItemVariantTitleDelProc 
 */

CREATE PROCEDURE ItemVariantTitleDelProc
(
    @ItemVariantTitle     varchar(250))
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM ItemVariantTitle
     WHERE ItemVariantTitle = @ItemVariantTitle

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'ItemVariantTitleDelProc: Cannot delete because foreign keys still exist in ItemVariantTitle '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ItemVariantTitleDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemVariantTitleDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemVariantTitleDelProc >>>'
go


/* 
 * PROCEDURE: ItemVariantTitleSelProc 
 */

CREATE PROCEDURE ItemVariantTitleSelProc
(
    @ItemVariantTitle     varchar(250))
AS
BEGIN
    SELECT ItemId,
           ItemVariantTitle,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM ItemVariantTitle
     WHERE ItemVariantTitle = @ItemVariantTitle

    RETURN(0)
END
go
IF OBJECT_ID('ItemVariantTitleSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ItemVariantTitleSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ItemVariantTitleSelProc >>>'
go


/* 
 * PROCEDURE: OperatingSystemInsProc 
 */

CREATE PROCEDURE OperatingSystemInsProc
(
    @OperatingSystemName              varchar(100),
    @OperatingSystemVersion           varchar(20)             = NULL,
    @OperatingSystemLocation          varchar(50)             = NULL,
    @OperatingSystemDocumentation     varchar(100)            = NULL,
    @CreatedDate                      datetime,
    @CreatedBy                        varchar(50),
    @ModifiedDate                     datetime                = NULL,
    @ModifiedBy                       varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO OperatingSystem(OperatingSystemName,
                                OperatingSystemVersion,
                                OperatingSystemLocation,
                                OperatingSystemDocumentation,
                                CreatedDate,
                                CreatedBy,
                                ModifiedDate,
                                ModifiedBy)
    VALUES(@OperatingSystemName,
           @OperatingSystemVersion,
           @OperatingSystemLocation,
           @OperatingSystemDocumentation,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'OperatingSystemInsProc: Cannot insert because primary key value not found in OperatingSystem '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('OperatingSystemInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE OperatingSystemInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE OperatingSystemInsProc >>>'
go


/* 
 * PROCEDURE: OperatingSystemUpdProc 
 */

CREATE PROCEDURE OperatingSystemUpdProc
(
    @OperatingSystemId                int,
    @OperatingSystemName              varchar(100),
    @OperatingSystemVersion           varchar(20)             = NULL,
    @OperatingSystemLocation          varchar(50)             = NULL,
    @OperatingSystemDocumentation     varchar(100)            = NULL,
    @CreatedDate                      datetime,
    @CreatedBy                        varchar(50),
    @ModifiedDate                     datetime                = NULL,
    @ModifiedBy                       varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE OperatingSystem
       SET OperatingSystemName               = @OperatingSystemName,
           OperatingSystemVersion            = @OperatingSystemVersion,
           OperatingSystemLocation           = @OperatingSystemLocation,
           OperatingSystemDocumentation      = @OperatingSystemDocumentation,
           CreatedDate                       = @CreatedDate,
           CreatedBy                         = @CreatedBy,
           ModifiedDate                      = @ModifiedDate,
           ModifiedBy                        = @ModifiedBy
     WHERE OperatingSystemId = @OperatingSystemId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'OperatingSystemUpdProc: Cannot update  in OperatingSystem '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('OperatingSystemUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE OperatingSystemUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE OperatingSystemUpdProc >>>'
go


/* 
 * PROCEDURE: OperatingSystemDelProc 
 */

CREATE PROCEDURE OperatingSystemDelProc
(
    @OperatingSystemId                int)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM OperatingSystem
     WHERE OperatingSystemId = @OperatingSystemId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'OperatingSystemDelProc: Cannot delete because foreign keys still exist in OperatingSystem '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('OperatingSystemDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE OperatingSystemDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE OperatingSystemDelProc >>>'
go


/* 
 * PROCEDURE: OperatingSystemSelProc 
 */

CREATE PROCEDURE OperatingSystemSelProc
(
    @OperatingSystemId                int)
AS
BEGIN
    SELECT OperatingSystemId,
           OperatingSystemName,
           OperatingSystemVersion,
           OperatingSystemLocation,
           OperatingSystemDocumentation,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM OperatingSystem
     WHERE OperatingSystemId = @OperatingSystemId

    RETURN(0)
END
go
IF OBJECT_ID('OperatingSystemSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE OperatingSystemSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE OperatingSystemSelProc >>>'
go


/* 
 * PROCEDURE: ProvenanceInsProc 
 */

CREATE PROCEDURE ProvenanceInsProc
(
    @ProvenanceName                      varchar(100),
    @ProvenanceAdministrativeHistory     varchar(max)            = NULL,
    @CreatedDate                         datetime,
    @CreatedBy                           varchar(50),
    @ModifiedDate                        datetime                = NULL,
    @ModifiedBy                          varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO Provenance(ProvenanceName,
                           ProvenanceAdministrativeHistory,
                           CreatedDate,
                           CreatedBy,
                           ModifiedDate,
                           ModifiedBy)
    VALUES(@ProvenanceName,
           @ProvenanceAdministrativeHistory,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'ProvenanceInsProc: Cannot insert because primary key value not found in Provenance '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('ProvenanceInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ProvenanceInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ProvenanceInsProc >>>'
go


/* 
 * PROCEDURE: ProvenanceUpdProc 
 */

CREATE PROCEDURE ProvenanceUpdProc
(
    @ProvenanceId                        int,
    @ProvenanceName                      varchar(100),
    @ProvenanceAdministrativeHistory     varchar(max)            = NULL,
    @CreatedDate                         datetime,
    @CreatedBy                           varchar(50),
    @ModifiedDate                        datetime                = NULL,
    @ModifiedBy                          varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE Provenance
       SET ProvenanceName                       = @ProvenanceName,
           ProvenanceAdministrativeHistory      = @ProvenanceAdministrativeHistory,
           CreatedDate                          = @CreatedDate,
           CreatedBy                            = @CreatedBy,
           ModifiedDate                         = @ModifiedDate,
           ModifiedBy                           = @ModifiedBy
     WHERE ProvenanceId = @ProvenanceId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'ProvenanceUpdProc: Cannot update  in Provenance '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ProvenanceUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ProvenanceUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ProvenanceUpdProc >>>'
go


/* 
 * PROCEDURE: ProvenanceDelProc 
 */

CREATE PROCEDURE ProvenanceDelProc
(
    @ProvenanceId                        int)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM Provenance
     WHERE ProvenanceId = @ProvenanceId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'ProvenanceDelProc: Cannot delete because foreign keys still exist in Provenance '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ProvenanceDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ProvenanceDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ProvenanceDelProc >>>'
go


/* 
 * PROCEDURE: ProvenanceSelProc 
 */

CREATE PROCEDURE ProvenanceSelProc
(
    @ProvenanceId                        int)
AS
BEGIN
    SELECT ProvenanceId,
           ProvenanceName,
           ProvenanceAdministrativeHistory,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM Provenance
     WHERE ProvenanceId = @ProvenanceId

    RETURN(0)
END
go
IF OBJECT_ID('ProvenanceSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ProvenanceSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ProvenanceSelProc >>>'
go


/* 
 * PROCEDURE: ProvenanceVariantInsProc 
 */

CREATE PROCEDURE ProvenanceVariantInsProc
(
    @ProvenanceId                     int,
    @ProvenanceVariantQualifierId     int,
    @ProvenanceVariantName            varchar(100),
    @CreatedDate                      datetime,
    @CreatedBy                        varchar(50),
    @ModifiedDate                     datetime                = NULL,
    @ModifiedBy                       varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO ProvenanceVariant(ProvenanceId,
                                  ProvenanceVariantQualifierId,
                                  ProvenanceVariantName,
                                  CreatedDate,
                                  CreatedBy,
                                  ModifiedDate,
                                  ModifiedBy)
    VALUES(@ProvenanceId,
           @ProvenanceVariantQualifierId,
           @ProvenanceVariantName,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'ProvenanceVariantInsProc: Cannot insert because primary key value not found in ProvenanceVariant '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('ProvenanceVariantInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ProvenanceVariantInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ProvenanceVariantInsProc >>>'
go


/* 
 * PROCEDURE: ProvenanceVariantUpdProc 
 */

CREATE PROCEDURE ProvenanceVariantUpdProc
(
    @ProvenanceVariantId              int,
    @ProvenanceId                     int,
    @ProvenanceVariantQualifierId     int,
    @ProvenanceVariantName            varchar(100),
    @CreatedDate                      datetime,
    @CreatedBy                        varchar(50),
    @ModifiedDate                     datetime                = NULL,
    @ModifiedBy                       varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE ProvenanceVariant
       SET CreatedDate                       = @CreatedDate,
           CreatedBy                         = @CreatedBy,
           ModifiedDate                      = @ModifiedDate,
           ModifiedBy                        = @ModifiedBy
     WHERE ProvenanceId                 = @ProvenanceId
       AND ProvenanceVariantQualifierId = @ProvenanceVariantQualifierId
       AND ProvenanceVariantName        = @ProvenanceVariantName

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'ProvenanceVariantUpdProc: Cannot update  in ProvenanceVariant '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ProvenanceVariantUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ProvenanceVariantUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ProvenanceVariantUpdProc >>>'
go


/* 
 * PROCEDURE: ProvenanceVariantDelProc 
 */

CREATE PROCEDURE ProvenanceVariantDelProc
(
    @ProvenanceId                     int,
    @ProvenanceVariantQualifierId     int,
    @ProvenanceVariantName            varchar(100))
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM ProvenanceVariant
     WHERE ProvenanceId                 = @ProvenanceId
       AND ProvenanceVariantQualifierId = @ProvenanceVariantQualifierId
       AND ProvenanceVariantName        = @ProvenanceVariantName

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'ProvenanceVariantDelProc: Cannot delete because foreign keys still exist in ProvenanceVariant '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('ProvenanceVariantDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ProvenanceVariantDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ProvenanceVariantDelProc >>>'
go


/* 
 * PROCEDURE: ProvenanceVariantSelProc 
 */

CREATE PROCEDURE ProvenanceVariantSelProc
(
    @ProvenanceId                     int,
    @ProvenanceVariantQualifierId     int,
    @ProvenanceVariantName            varchar(100))
AS
BEGIN
    SELECT ProvenanceVariantId,
           ProvenanceId,
           ProvenanceVariantQualifierId,
           ProvenanceVariantName,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM ProvenanceVariant
     WHERE ProvenanceId                 = @ProvenanceId
       AND ProvenanceVariantQualifierId = @ProvenanceVariantQualifierId
       AND ProvenanceVariantName        = @ProvenanceVariantName

    RETURN(0)
END
go
IF OBJECT_ID('ProvenanceVariantSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE ProvenanceVariantSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE ProvenanceVariantSelProc >>>'
go


/* 
 * PROCEDURE: QualifierInsProc 
 */

CREATE PROCEDURE QualifierInsProc
(
    @QualifierType     varchar(50),
    @QualifierName     varchar(50),
    @CreatedBy         varchar(50),
    @CreatedDate       datetime,
    @ModifiedBy        varchar(50)            = NULL,
    @ModifiedDate      datetime               = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO Qualifier(QualifierType,
                          QualifierName,
                          CreatedBy,
                          CreatedDate,
                          ModifiedBy,
                          ModifiedDate)
    VALUES(@QualifierType,
           @QualifierName,
           @CreatedBy,
           @CreatedDate,
           @ModifiedBy,
           @ModifiedDate)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'QualifierInsProc: Cannot insert because primary key value not found in Qualifier '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('QualifierInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE QualifierInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE QualifierInsProc >>>'
go


/* 
 * PROCEDURE: QualifierUpdProc 
 */

CREATE PROCEDURE QualifierUpdProc
(
    @QualifierId       int,
    @QualifierType     varchar(50),
    @QualifierName     varchar(50),
    @CreatedBy         varchar(50),
    @CreatedDate       datetime,
    @ModifiedBy        varchar(50)            = NULL,
    @ModifiedDate      datetime               = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE Qualifier
       SET QualifierType      = @QualifierType,
           QualifierName      = @QualifierName,
           CreatedBy          = @CreatedBy,
           CreatedDate        = @CreatedDate,
           ModifiedBy         = @ModifiedBy,
           ModifiedDate       = @ModifiedDate
     WHERE QualifierId = @QualifierId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'QualifierUpdProc: Cannot update  in Qualifier '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('QualifierUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE QualifierUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE QualifierUpdProc >>>'
go


/* 
 * PROCEDURE: QualifierDelProc 
 */

CREATE PROCEDURE QualifierDelProc
(
    @QualifierId       int)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM Qualifier
     WHERE QualifierId = @QualifierId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'QualifierDelProc: Cannot delete because foreign keys still exist in Qualifier '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('QualifierDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE QualifierDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE QualifierDelProc >>>'
go


/* 
 * PROCEDURE: QualifierSelProc 
 */

CREATE PROCEDURE QualifierSelProc
(
    @QualifierId       int)
AS
BEGIN
    SELECT QualifierId,
           QualifierType,
           QualifierName,
           CreatedBy,
           CreatedDate,
           ModifiedBy,
           ModifiedDate
      FROM Qualifier
     WHERE QualifierId = @QualifierId

    RETURN(0)
END
go
IF OBJECT_ID('QualifierSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE QualifierSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE QualifierSelProc >>>'
go


/* 
 * PROCEDURE: RelatedItemInsProc 
 */

CREATE PROCEDURE RelatedItemInsProc
(
    @ItemId                int,
    @RelatedItemId         int,
    @RelatedItemTypeId     int,
    @CreatedDate           datetime,
    @CreatedBy             varchar(50),
    @ModifiedDate          datetime               = NULL,
    @ModifiedBy            varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO RelatedItem(ItemId,
                            RelatedItemId,
                            RelatedItemTypeId,
                            CreatedDate,
                            CreatedBy,
                            ModifiedDate,
                            ModifiedBy)
    VALUES(@ItemId,
           @RelatedItemId,
           @RelatedItemTypeId,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'RelatedItemInsProc: Cannot insert because primary key value not found in RelatedItem '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('RelatedItemInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE RelatedItemInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE RelatedItemInsProc >>>'
go


/* 
 * PROCEDURE: RelatedItemUpdProc 
 */

CREATE PROCEDURE RelatedItemUpdProc
(
    @ItemId                int,
    @RelatedItemId         int,
    @RelatedItemTypeId     int,
    @CreatedDate           datetime,
    @CreatedBy             varchar(50),
    @ModifiedDate          datetime               = NULL,
    @ModifiedBy            varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE RelatedItem
       SET CreatedDate            = @CreatedDate,
           CreatedBy              = @CreatedBy,
           ModifiedDate           = @ModifiedDate,
           ModifiedBy             = @ModifiedBy
     WHERE ItemId            = @ItemId
       AND RelatedItemId     = @RelatedItemId
       AND RelatedItemTypeId = @RelatedItemTypeId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'RelatedItemUpdProc: Cannot update  in RelatedItem '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('RelatedItemUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE RelatedItemUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE RelatedItemUpdProc >>>'
go


/* 
 * PROCEDURE: RelatedItemDelProc 
 */

CREATE PROCEDURE RelatedItemDelProc
(
    @ItemId                int,
    @RelatedItemId         int,
    @RelatedItemTypeId     int)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM RelatedItem
     WHERE ItemId            = @ItemId
       AND RelatedItemId     = @RelatedItemId
       AND RelatedItemTypeId = @RelatedItemTypeId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'RelatedItemDelProc: Cannot delete because foreign keys still exist in RelatedItem '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('RelatedItemDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE RelatedItemDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE RelatedItemDelProc >>>'
go


/* 
 * PROCEDURE: RelatedItemSelProc 
 */

CREATE PROCEDURE RelatedItemSelProc
(
    @ItemId                int,
    @RelatedItemId         int,
    @RelatedItemTypeId     int)
AS
BEGIN
    SELECT ItemId,
           RelatedItemId,
           RelatedItemTypeId,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM RelatedItem
     WHERE ItemId            = @ItemId
       AND RelatedItemId     = @RelatedItemId
       AND RelatedItemTypeId = @RelatedItemTypeId

    RETURN(0)
END
go
IF OBJECT_ID('RelatedItemSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE RelatedItemSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE RelatedItemSelProc >>>'
go


/* 
 * PROCEDURE: RelatedItemTypeInsProc 
 */

CREATE PROCEDURE RelatedItemTypeInsProc
(
    @RelatedItemTypeName     varchar(50),
    @CreatedDate             datetime,
    @CreatedBy               varchar(50),
    @ModifiedDate            datetime               = NULL,
    @ModifiedBy              varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO RelatedItemType(RelatedItemTypeName,
                                CreatedDate,
                                CreatedBy,
                                ModifiedDate,
                                ModifiedBy)
    VALUES(@RelatedItemTypeName,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'RelatedItemTypeInsProc: Cannot insert because primary key value not found in RelatedItemType '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('RelatedItemTypeInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE RelatedItemTypeInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE RelatedItemTypeInsProc >>>'
go


/* 
 * PROCEDURE: RelatedItemTypeUpdProc 
 */

CREATE PROCEDURE RelatedItemTypeUpdProc
(
    @RelatedItemTypeId       int,
    @RelatedItemTypeName     varchar(50),
    @CreatedDate             datetime,
    @CreatedBy               varchar(50),
    @ModifiedDate            datetime               = NULL,
    @ModifiedBy              varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE RelatedItemType
       SET RelatedItemTypeName      = @RelatedItemTypeName,
           CreatedDate              = @CreatedDate,
           CreatedBy                = @CreatedBy,
           ModifiedDate             = @ModifiedDate,
           ModifiedBy               = @ModifiedBy
     WHERE RelatedItemTypeId = @RelatedItemTypeId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'RelatedItemTypeUpdProc: Cannot update  in RelatedItemType '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('RelatedItemTypeUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE RelatedItemTypeUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE RelatedItemTypeUpdProc >>>'
go


/* 
 * PROCEDURE: RelatedItemTypeDelProc 
 */

CREATE PROCEDURE RelatedItemTypeDelProc
(
    @RelatedItemTypeId       int)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM RelatedItemType
     WHERE RelatedItemTypeId = @RelatedItemTypeId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'RelatedItemTypeDelProc: Cannot delete because foreign keys still exist in RelatedItemType '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('RelatedItemTypeDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE RelatedItemTypeDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE RelatedItemTypeDelProc >>>'
go


/* 
 * PROCEDURE: RelatedItemTypeSelProc 
 */

CREATE PROCEDURE RelatedItemTypeSelProc
(
    @RelatedItemTypeId       int)
AS
BEGIN
    SELECT RelatedItemTypeId,
           RelatedItemTypeName,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM RelatedItemType
     WHERE RelatedItemTypeId = @RelatedItemTypeId

    RETURN(0)
END
go
IF OBJECT_ID('RelatedItemTypeSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE RelatedItemTypeSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE RelatedItemTypeSelProc >>>'
go


/* 
 * PROCEDURE: SeriesInsProc 
 */

CREATE PROCEDURE SeriesInsProc
(
    @SeriesTitle                      varchar(250),
    @SeriesDescription                varchar(max)             = NULL,
    @SeriesOpenDate                   varchar(50)              = NULL,
    @SeriesStartYear                  int                      = NULL,
    @SeriesEndYear                    int                      = NULL,
    @SeriesDateRangeDescription       varchar(100)             = NULL,
    @SeriesExtentDescription          varchar(1024)            = NULL,
    @SeriesArrangement                varchar(1024)            = NULL,
    @SeriesRestrictionDescription     varchar(1024)            = NULL,
    @SeriesGeneralNote                varchar(max)             = NULL,
    @SeriesIdentifier                 varchar(50)              = NULL,
    @SeriesActivity                   varchar(50)              = NULL,
    @ProvenanceId                     int                      = NULL,
    @CreatedDate                      datetime,
    @CreatedBy                        varchar(50),
    @ModifiedDate                     datetime                 = NULL,
    @ModifiedBy                       varchar(50)              = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO Series(SeriesTitle,
                       SeriesDescription,
                       SeriesOpenDate,
                       SeriesStartYear,
                       SeriesEndYear,
                       SeriesDateRangeDescription,
                       SeriesExtentDescription,
                       SeriesArrangement,
                       SeriesRestrictionDescription,
                       SeriesGeneralNote,
                       SeriesIdentifier,
                       SeriesActivity,
                       ProvenanceId,
                       CreatedDate,
                       CreatedBy,
                       ModifiedDate,
                       ModifiedBy)
    VALUES(@SeriesTitle,
           @SeriesDescription,
           @SeriesOpenDate,
           @SeriesStartYear,
           @SeriesEndYear,
           @SeriesDateRangeDescription,
           @SeriesExtentDescription,
           @SeriesArrangement,
           @SeriesRestrictionDescription,
           @SeriesGeneralNote,
           @SeriesIdentifier,
           @SeriesActivity,
           @ProvenanceId,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'SeriesInsProc: Cannot insert because primary key value not found in Series '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('SeriesInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SeriesInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SeriesInsProc >>>'
go


/* 
 * PROCEDURE: SeriesUpdProc 
 */

CREATE PROCEDURE SeriesUpdProc
(
    @SeriesId                         int,
    @SeriesTitle                      varchar(250),
    @SeriesDescription                varchar(max)             = NULL,
    @SeriesOpenDate                   varchar(50)              = NULL,
    @SeriesStartYear                  int                      = NULL,
    @SeriesEndYear                    int                      = NULL,
    @SeriesDateRangeDescription       varchar(100)             = NULL,
    @SeriesExtentDescription          varchar(1024)            = NULL,
    @SeriesArrangement                varchar(1024)            = NULL,
    @SeriesRestrictionDescription     varchar(1024)            = NULL,
    @SeriesGeneralNote                varchar(max)             = NULL,
    @SeriesIdentifier                 varchar(50)              = NULL,
    @SeriesActivity                   varchar(50)              = NULL,
    @ProvenanceId                     int                      = NULL,
    @CreatedDate                      datetime,
    @CreatedBy                        varchar(50),
    @ModifiedDate                     datetime                 = NULL,
    @ModifiedBy                       varchar(50)              = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE Series
       SET SeriesTitle                       = @SeriesTitle,
           SeriesDescription                 = @SeriesDescription,
           SeriesOpenDate                    = @SeriesOpenDate,
           SeriesStartYear                   = @SeriesStartYear,
           SeriesEndYear                     = @SeriesEndYear,
           SeriesDateRangeDescription        = @SeriesDateRangeDescription,
           SeriesExtentDescription           = @SeriesExtentDescription,
           SeriesArrangement                 = @SeriesArrangement,
           SeriesRestrictionDescription      = @SeriesRestrictionDescription,
           SeriesGeneralNote                 = @SeriesGeneralNote,
           SeriesIdentifier                  = @SeriesIdentifier,
           SeriesActivity                    = @SeriesActivity,
           ProvenanceId                      = @ProvenanceId,
           CreatedDate                       = @CreatedDate,
           CreatedBy                         = @CreatedBy,
           ModifiedDate                      = @ModifiedDate,
           ModifiedBy                        = @ModifiedBy
     WHERE SeriesId = @SeriesId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'SeriesUpdProc: Cannot update  in Series '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('SeriesUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SeriesUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SeriesUpdProc >>>'
go


/* 
 * PROCEDURE: SeriesDelProc 
 */

CREATE PROCEDURE SeriesDelProc
(
    @SeriesId                         int)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM Series
     WHERE SeriesId = @SeriesId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'SeriesDelProc: Cannot delete because foreign keys still exist in Series '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('SeriesDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SeriesDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SeriesDelProc >>>'
go


/* 
 * PROCEDURE: SeriesSelProc 
 */

CREATE PROCEDURE SeriesSelProc
(
    @SeriesId                         int)
AS
BEGIN
    SELECT SeriesId,
           SeriesTitle,
           SeriesDescription,
           SeriesOpenDate,
           SeriesStartYear,
           SeriesEndYear,
           SeriesDateRangeDescription,
           SeriesExtentDescription,
           SeriesArrangement,
           SeriesRestrictionDescription,
           SeriesGeneralNote,
           SeriesIdentifier,
           SeriesActivity,
           ProvenanceId,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM Series
     WHERE SeriesId = @SeriesId

    RETURN(0)
END
go
IF OBJECT_ID('SeriesSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SeriesSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SeriesSelProc >>>'
go


/* 
 * PROCEDURE: SeriesAcquisitionInsProc 
 */

CREATE PROCEDURE SeriesAcquisitionInsProc
(
    @SeriesId                               int,
    @SeriesAcquisitionIngestDate            datetime                = NULL,
    @SeriesAcquisitionReadyToIngest         bit,
    @SeriesAcquisitionIngested              bit,
    @SeriesAcquisitionAccessionNumber       varchar(50)             = NULL,
    @SeriesAcquisitionTransferAuthority     varchar(50)             = NULL,
    @SeriesAcquisitionSource                varchar(100)            = NULL,
    @SeriesAcquisitionDirectory             varchar(255)            = NULL,
    @SeriesAcquisitionReceivedDate          datetime                = NULL,
    @CreatedDate                            datetime,
    @CreatedBy                              varchar(50),
    @ModifiedDate                           datetime                = NULL,
    @ModifiedBy                             varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO SeriesAcquisition(SeriesId,
                                  SeriesAcquisitionIngestDate,
                                  SeriesAcquisitionReadyToIngest,
                                  SeriesAcquisitionIngested,
                                  SeriesAcquisitionAccessionNumber,
                                  SeriesAcquisitionTransferAuthority,
                                  SeriesAcquisitionSource,
                                  SeriesAcquisitionDirectory,
                                  SeriesAcquisitionReceivedDate,
                                  CreatedDate,
                                  CreatedBy,
                                  ModifiedDate,
                                  ModifiedBy)
    VALUES(@SeriesId,
           @SeriesAcquisitionIngestDate,
           @SeriesAcquisitionReadyToIngest,
           @SeriesAcquisitionIngested,
           @SeriesAcquisitionAccessionNumber,
           @SeriesAcquisitionTransferAuthority,
           @SeriesAcquisitionSource,
           @SeriesAcquisitionDirectory,
           @SeriesAcquisitionReceivedDate,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'SeriesAcquisitionInsProc: Cannot insert because primary key value not found in SeriesAcquisition '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('SeriesAcquisitionInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SeriesAcquisitionInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SeriesAcquisitionInsProc >>>'
go


/* 
 * PROCEDURE: SeriesAcquisitionUpdProc 
 */

CREATE PROCEDURE SeriesAcquisitionUpdProc
(
    @SeriesAcquisitionId                    int,
    @SeriesId                               int,
    @SeriesAcquisitionIngestDate            datetime                = NULL,
    @SeriesAcquisitionReadyToIngest         bit,
    @SeriesAcquisitionIngested              bit,
    @SeriesAcquisitionAccessionNumber       varchar(50)             = NULL,
    @SeriesAcquisitionTransferAuthority     varchar(50)             = NULL,
    @SeriesAcquisitionSource                varchar(100)            = NULL,
    @SeriesAcquisitionDirectory             varchar(255)            = NULL,
    @SeriesAcquisitionReceivedDate          datetime                = NULL,
    @CreatedDate                            datetime,
    @CreatedBy                              varchar(50),
    @ModifiedDate                           datetime                = NULL,
    @ModifiedBy                             varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE SeriesAcquisition
       SET SeriesId                                = @SeriesId,
           SeriesAcquisitionIngestDate             = @SeriesAcquisitionIngestDate,
           SeriesAcquisitionReadyToIngest          = @SeriesAcquisitionReadyToIngest,
           SeriesAcquisitionIngested               = @SeriesAcquisitionIngested,
           SeriesAcquisitionAccessionNumber        = @SeriesAcquisitionAccessionNumber,
           SeriesAcquisitionTransferAuthority      = @SeriesAcquisitionTransferAuthority,
           SeriesAcquisitionSource                 = @SeriesAcquisitionSource,
           SeriesAcquisitionDirectory              = @SeriesAcquisitionDirectory,
           SeriesAcquisitionReceivedDate           = @SeriesAcquisitionReceivedDate,
           CreatedDate                             = @CreatedDate,
           CreatedBy                               = @CreatedBy,
           ModifiedDate                            = @ModifiedDate,
           ModifiedBy                              = @ModifiedBy
     WHERE SeriesAcquisitionId = @SeriesAcquisitionId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'SeriesAcquisitionUpdProc: Cannot update  in SeriesAcquisition '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('SeriesAcquisitionUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SeriesAcquisitionUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SeriesAcquisitionUpdProc >>>'
go


/* 
 * PROCEDURE: SeriesAcquisitionDelProc 
 */

CREATE PROCEDURE SeriesAcquisitionDelProc
(
    @SeriesAcquisitionId                    int)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM SeriesAcquisition
     WHERE SeriesAcquisitionId = @SeriesAcquisitionId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'SeriesAcquisitionDelProc: Cannot delete because foreign keys still exist in SeriesAcquisition '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('SeriesAcquisitionDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SeriesAcquisitionDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SeriesAcquisitionDelProc >>>'
go


/* 
 * PROCEDURE: SeriesAcquisitionSelProc 
 */

CREATE PROCEDURE SeriesAcquisitionSelProc
(
    @SeriesAcquisitionId                    int)
AS
BEGIN
    SELECT SeriesAcquisitionId,
           SeriesId,
           SeriesAcquisitionIngestDate,
           SeriesAcquisitionReadyToIngest,
           SeriesAcquisitionIngested,
           SeriesAcquisitionAccessionNumber,
           SeriesAcquisitionTransferAuthority,
           SeriesAcquisitionSource,
           SeriesAcquisitionDirectory,
           SeriesAcquisitionReceivedDate,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM SeriesAcquisition
     WHERE SeriesAcquisitionId = @SeriesAcquisitionId

    RETURN(0)
END
go
IF OBJECT_ID('SeriesAcquisitionSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SeriesAcquisitionSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SeriesAcquisitionSelProc >>>'
go


/* 
 * PROCEDURE: SeriesAcquisitionItemInsProc 
 */

CREATE PROCEDURE SeriesAcquisitionItemInsProc
(
    @SeriesId                int,
    @ItemId                  int,
    @SeriesAcquisitionId     int,
    @CreatedDate             datetime,
    @CreatedBy               varchar(50),
    @ModifiedDate            datetime               = NULL,
    @ModifiedBy              varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO SeriesAcquisitionItem(SeriesId,
                                      ItemId,
                                      SeriesAcquisitionId,
                                      CreatedDate,
                                      CreatedBy,
                                      ModifiedDate,
                                      ModifiedBy)
    VALUES(@SeriesId,
           @ItemId,
           @SeriesAcquisitionId,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'SeriesAcquisitionItemInsProc: Cannot insert because primary key value not found in SeriesAcquisitionItem '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('SeriesAcquisitionItemInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SeriesAcquisitionItemInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SeriesAcquisitionItemInsProc >>>'
go


/* 
 * PROCEDURE: SeriesAcquisitionItemUpdProc 
 */

CREATE PROCEDURE SeriesAcquisitionItemUpdProc
(
    @SeriesId                int,
    @ItemId                  int,
    @SeriesAcquisitionId     int,
    @CreatedDate             datetime,
    @CreatedBy               varchar(50),
    @ModifiedDate            datetime               = NULL,
    @ModifiedBy              varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE SeriesAcquisitionItem
       SET SeriesAcquisitionId      = @SeriesAcquisitionId,
           CreatedDate              = @CreatedDate,
           CreatedBy                = @CreatedBy,
           ModifiedDate             = @ModifiedDate,
           ModifiedBy               = @ModifiedBy
     WHERE SeriesId = @SeriesId
       AND ItemId   = @ItemId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'SeriesAcquisitionItemUpdProc: Cannot update  in SeriesAcquisitionItem '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('SeriesAcquisitionItemUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SeriesAcquisitionItemUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SeriesAcquisitionItemUpdProc >>>'
go


/* 
 * PROCEDURE: SeriesAcquisitionItemDelProc 
 */

CREATE PROCEDURE SeriesAcquisitionItemDelProc
(
    @SeriesId                int,
    @ItemId                  int)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM SeriesAcquisitionItem
     WHERE SeriesId = @SeriesId
       AND ItemId   = @ItemId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'SeriesAcquisitionItemDelProc: Cannot delete because foreign keys still exist in SeriesAcquisitionItem '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('SeriesAcquisitionItemDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SeriesAcquisitionItemDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SeriesAcquisitionItemDelProc >>>'
go


/* 
 * PROCEDURE: SeriesAcquisitionItemSelProc 
 */

CREATE PROCEDURE SeriesAcquisitionItemSelProc
(
    @SeriesId                int,
    @ItemId                  int)
AS
BEGIN
    SELECT SeriesId,
           ItemId,
           SeriesAcquisitionId,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM SeriesAcquisitionItem
     WHERE SeriesId = @SeriesId
       AND ItemId   = @ItemId

    RETURN(0)
END
go
IF OBJECT_ID('SeriesAcquisitionItemSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SeriesAcquisitionItemSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SeriesAcquisitionItemSelProc >>>'
go


/* 
 * PROCEDURE: SeriesKeywordInsProc 
 */

CREATE PROCEDURE SeriesKeywordInsProc
(
    @SeriesId            int,
    @SeriesKeyword       varchar(50),
    @CreatedDate         datetime,
    @CreatedBy           varchar(50),
    @ModifiedDate        datetime               = NULL,
    @ModifiedBy          varchar(50)            = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO SeriesKeyword(SeriesId,
                              SeriesKeyword,
                              CreatedDate,
                              CreatedBy,
                              ModifiedDate,
                              ModifiedBy 
                              )
    VALUES(@SeriesId,
           @SeriesKeyword,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy 
           )

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'SeriesKeywordInsProc: Cannot insert because primary key value not found in SeriesKeyword '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('SeriesKeywordInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SeriesKeywordInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SeriesKeywordInsProc >>>'
go


/* 
 * PROCEDURE: SeriesKeywordUpdProc 
 */

CREATE PROCEDURE SeriesKeywordUpdProc
(
    @SeriesId            int,
    @SeriesKeyword       varchar(50),
    @CreatedDate         datetime,
    @CreatedBy           varchar(50),
    @ModifiedDate        datetime               = NULL,
    @ModifiedBy          varchar(50)            = NULL,
    @SeriesKeywordId     int)
AS
BEGIN
    BEGIN TRAN

    UPDATE SeriesKeyword
       SET CreatedDate          = @CreatedDate,
           CreatedBy            = @CreatedBy,
           ModifiedDate         = @ModifiedDate,
           ModifiedBy           = @ModifiedBy
     WHERE SeriesId      = @SeriesId
       AND SeriesKeyword = @SeriesKeyword

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'SeriesKeywordUpdProc: Cannot update  in SeriesKeyword '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('SeriesKeywordUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SeriesKeywordUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SeriesKeywordUpdProc >>>'
go


/* 
 * PROCEDURE: SeriesKeywordDelProc 
 */

CREATE PROCEDURE SeriesKeywordDelProc
(
    @SeriesId            int,
    @SeriesKeyword       varchar(50))
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM SeriesKeyword
     WHERE SeriesId      = @SeriesId
       AND SeriesKeyword = @SeriesKeyword

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'SeriesKeywordDelProc: Cannot delete because foreign keys still exist in SeriesKeyword '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('SeriesKeywordDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SeriesKeywordDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SeriesKeywordDelProc >>>'
go


/* 
 * PROCEDURE: SeriesKeywordSelProc 
 */

CREATE PROCEDURE SeriesKeywordSelProc
(
    @SeriesId            int,
    @SeriesKeyword       varchar(50))
AS
BEGIN
    SELECT SeriesId,
           SeriesKeyword,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy,
           SeriesKeywordId
      FROM SeriesKeyword
     WHERE SeriesId      = @SeriesId
       AND SeriesKeyword = @SeriesKeyword

    RETURN(0)
END
go
IF OBJECT_ID('SeriesKeywordSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SeriesKeywordSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SeriesKeywordSelProc >>>'
go


/* 
 * PROCEDURE: SeriesLocationInsProc 
 */

CREATE PROCEDURE SeriesLocationInsProc
(
    @SeriesId                   int,
    @SeriesGeoLocationValue     varchar(100),
    @CreatedDate                datetime,
    @CreatedBy                  varchar(50),
    @ModifiedDate               datetime                = NULL,
    @ModifiedBy                 varchar(50)             = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO SeriesLocation(SeriesId,
                               SeriesGeoLocationValue,
                               CreatedDate,
                               CreatedBy,
                               ModifiedDate,
                               ModifiedBy 
                               )
    VALUES(@SeriesId,
           @SeriesGeoLocationValue,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy 
           )

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'SeriesLocationInsProc: Cannot insert because primary key value not found in SeriesLocation '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('SeriesLocationInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SeriesLocationInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SeriesLocationInsProc >>>'
go


/* 
 * PROCEDURE: SeriesLocationUpdProc 
 */

CREATE PROCEDURE SeriesLocationUpdProc
(
    @SeriesId                   int,
    @SeriesGeoLocationValue     varchar(100),
    @CreatedDate                datetime,
    @CreatedBy                  varchar(50),
    @ModifiedDate               datetime                = NULL,
    @ModifiedBy                 varchar(50)             = NULL,
    @SeriesLocationId           int)
AS
BEGIN
    BEGIN TRAN

    UPDATE SeriesLocation
       SET CreatedDate                 = @CreatedDate,
           CreatedBy                   = @CreatedBy,
           ModifiedDate                = @ModifiedDate,
           ModifiedBy                  = @ModifiedBy
     WHERE SeriesId               = @SeriesId
       AND SeriesGeoLocationValue = @SeriesGeoLocationValue

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'SeriesLocationUpdProc: Cannot update  in SeriesLocation '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('SeriesLocationUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SeriesLocationUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SeriesLocationUpdProc >>>'
go


/* 
 * PROCEDURE: SeriesLocationDelProc 
 */

CREATE PROCEDURE SeriesLocationDelProc
(
    @SeriesId                   int,
    @SeriesGeoLocationValue     varchar(100))
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM SeriesLocation
     WHERE SeriesId               = @SeriesId
       AND SeriesGeoLocationValue = @SeriesGeoLocationValue

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'SeriesLocationDelProc: Cannot delete because foreign keys still exist in SeriesLocation '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('SeriesLocationDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SeriesLocationDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SeriesLocationDelProc >>>'
go


/* 
 * PROCEDURE: SeriesLocationSelProc 
 */

CREATE PROCEDURE SeriesLocationSelProc
(
    @SeriesId                   int,
    @SeriesGeoLocationValue     varchar(100))
AS
BEGIN
    SELECT SeriesId,
           SeriesGeoLocationValue,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy,
           SeriesLocationId
      FROM SeriesLocation
     WHERE SeriesId               = @SeriesId
       AND SeriesGeoLocationValue = @SeriesGeoLocationValue

    RETURN(0)
END
go
IF OBJECT_ID('SeriesLocationSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SeriesLocationSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SeriesLocationSelProc >>>'
go


/* 
 * PROCEDURE: SoftwareInsProc 
 */

CREATE PROCEDURE SoftwareInsProc
(
    @SoftwareName                          varchar(50),
    @SoftwareVersion                       varchar(20)              = NULL,
    @SoftwareDependencyDescription         varchar(1250)            = NULL,
    @SoftwarePurposeDescription            varchar(1250)            = NULL,
    @SoftwareCharacteristicDescription     varchar(1250)            = NULL,
    @SoftwareLocation                      varchar(50)              = NULL,
    @SoftwareNote                          varchar(max)             = NULL,
    @CreatedDate                           datetime,
    @CreatedBy                             varchar(50),
    @ModifiedDate                          datetime                 = NULL,
    @ModifiedBy                            varchar(50)              = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO Software(SoftwareName,
                         SoftwareVersion,
                         SoftwareDependencyDescription,
                         SoftwarePurposeDescription,
                         SoftwareCharacteristicDescription,
                         SoftwareLocation,
                         SoftwareNote,
                         CreatedDate,
                         CreatedBy,
                         ModifiedDate,
                         ModifiedBy)
    VALUES(@SoftwareName,
           @SoftwareVersion,
           @SoftwareDependencyDescription,
           @SoftwarePurposeDescription,
           @SoftwareCharacteristicDescription,
           @SoftwareLocation,
           @SoftwareNote,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'SoftwareInsProc: Cannot insert because primary key value not found in Software '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('SoftwareInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SoftwareInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SoftwareInsProc >>>'
go


/* 
 * PROCEDURE: SoftwareUpdProc 
 */

CREATE PROCEDURE SoftwareUpdProc
(
    @SoftwareId                            int,
    @SoftwareName                          varchar(50),
    @SoftwareVersion                       varchar(20)              = NULL,
    @SoftwareDependencyDescription         varchar(1250)            = NULL,
    @SoftwarePurposeDescription            varchar(1250)            = NULL,
    @SoftwareCharacteristicDescription     varchar(1250)            = NULL,
    @SoftwareLocation                      varchar(50)              = NULL,
    @SoftwareNote                          varchar(max)             = NULL,
    @CreatedDate                           datetime,
    @CreatedBy                             varchar(50),
    @ModifiedDate                          datetime                 = NULL,
    @ModifiedBy                            varchar(50)              = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE Software
       SET SoftwareName                           = @SoftwareName,
           SoftwareVersion                        = @SoftwareVersion,
           SoftwareDependencyDescription          = @SoftwareDependencyDescription,
           SoftwarePurposeDescription             = @SoftwarePurposeDescription,
           SoftwareCharacteristicDescription      = @SoftwareCharacteristicDescription,
           SoftwareLocation                       = @SoftwareLocation,
           SoftwareNote                           = @SoftwareNote,
           CreatedDate                            = @CreatedDate,
           CreatedBy                              = @CreatedBy,
           ModifiedDate                           = @ModifiedDate,
           ModifiedBy                             = @ModifiedBy
     WHERE SoftwareId = @SoftwareId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'SoftwareUpdProc: Cannot update  in Software '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('SoftwareUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SoftwareUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SoftwareUpdProc >>>'
go


/* 
 * PROCEDURE: SoftwareDelProc 
 */

CREATE PROCEDURE SoftwareDelProc
(
    @SoftwareId                            int)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM Software
     WHERE SoftwareId = @SoftwareId

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'SoftwareDelProc: Cannot delete because foreign keys still exist in Software '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('SoftwareDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SoftwareDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SoftwareDelProc >>>'
go


/* 
 * PROCEDURE: SoftwareSelProc 
 */

CREATE PROCEDURE SoftwareSelProc
(
    @SoftwareId                            int)
AS
BEGIN
    SELECT SoftwareId,
           SoftwareName,
           SoftwareVersion,
           SoftwareDependencyDescription,
           SoftwarePurposeDescription,
           SoftwareCharacteristicDescription,
           SoftwareLocation,
           SoftwareNote,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy
      FROM Software
     WHERE SoftwareId = @SoftwareId

    RETURN(0)
END
go
IF OBJECT_ID('SoftwareSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SoftwareSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SoftwareSelProc >>>'
go


/* 
 * PROCEDURE: SubjectInsProc 
 */

CREATE PROCEDURE SubjectInsProc
(
    @SeriesId              int,
    @QualifierId           int,
    @SeriesSubjectName     varchar(100),
    @CreatedDate           datetime,
    @CreatedBy             varchar(50),
    @ModifiedDate          datetime                = NULL,
    @ModifiedBy            varchar(50)             = NULL,
    @ItemId                int                     = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO Subject(SeriesId,
                        QualifierId,
                        SeriesSubjectName,
                        CreatedDate,
                        CreatedBy,
                        ModifiedDate,
                        ModifiedBy,
                        ItemId)
    VALUES(@SeriesId,
           @QualifierId,
           @SeriesSubjectName,
           @CreatedDate,
           @CreatedBy,
           @ModifiedDate,
           @ModifiedBy,
           @ItemId)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'SubjectInsProc: Cannot insert because primary key value not found in Subject '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('SubjectInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SubjectInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SubjectInsProc >>>'
go


/* 
 * PROCEDURE: SubjectUpdProc 
 */

CREATE PROCEDURE SubjectUpdProc
(
    @SeriesId              int,
    @QualifierId           int,
    @SeriesSubjectName     varchar(100),
    @CreatedDate           datetime,
    @CreatedBy             varchar(50),
    @ModifiedDate          datetime                = NULL,
    @ModifiedBy            varchar(50)             = NULL,
    @SeriesSubjectId       int,
    @ItemId                int                     = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE Subject
       SET SeriesId               = @SeriesId,
           QualifierId            = @QualifierId,
           CreatedDate            = @CreatedDate,
           CreatedBy              = @CreatedBy,
           ModifiedDate           = @ModifiedDate,
           ModifiedBy             = @ModifiedBy,
           ItemId                 = @ItemId
     WHERE SeriesSubjectName = @SeriesSubjectName

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'SubjectUpdProc: Cannot update  in Subject '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('SubjectUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SubjectUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SubjectUpdProc >>>'
go


/* 
 * PROCEDURE: SubjectDelProc 
 */

CREATE PROCEDURE SubjectDelProc
(
    @SeriesSubjectName     varchar(100))
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM Subject
     WHERE SeriesSubjectName = @SeriesSubjectName

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'SubjectDelProc: Cannot delete because foreign keys still exist in Subject '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('SubjectDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SubjectDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SubjectDelProc >>>'
go


/* 
 * PROCEDURE: SubjectSelProc 
 */

CREATE PROCEDURE SubjectSelProc
(
    @SeriesSubjectName     varchar(100))
AS
BEGIN
    SELECT SeriesId,
           QualifierId,
           SeriesSubjectName,
           CreatedDate,
           CreatedBy,
           ModifiedDate,
           ModifiedBy,
           SeriesSubjectId,
           ItemId
      FROM Subject
     WHERE SeriesSubjectName = @SeriesSubjectName

    RETURN(0)
END
go
IF OBJECT_ID('SubjectSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE SubjectSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE SubjectSelProc >>>'
go


/* 
 * PROCEDURE: sysdiagramsInsProc 
 */

CREATE PROCEDURE sysdiagramsInsProc
(
    @name             char(10),
    @principal_id     int,
    @version          int                       = NULL,
    @definition       varbinary(max)            = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO sysdiagrams(name,
                            principal_id,
                            version,
                            definition)
    VALUES(@name,
           @principal_id,
           @version,
           @definition)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'sysdiagramsInsProc: Cannot insert because primary key value not found in sysdiagrams '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
go
IF OBJECT_ID('sysdiagramsInsProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE sysdiagramsInsProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE sysdiagramsInsProc >>>'
go


/* 
 * PROCEDURE: sysdiagramsUpdProc 
 */

CREATE PROCEDURE sysdiagramsUpdProc
(
    @name             char(10),
    @principal_id     int,
    @diagram_id       int,
    @version          int                       = NULL,
    @definition       varbinary(max)            = NULL)
AS
BEGIN
    BEGIN TRAN

    UPDATE sysdiagrams
       SET name              = @name,
           principal_id      = @principal_id,
           version           = @version,
           definition        = @definition
     WHERE diagram_id = @diagram_id

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'sysdiagramsUpdProc: Cannot update  in sysdiagrams '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('sysdiagramsUpdProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE sysdiagramsUpdProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE sysdiagramsUpdProc >>>'
go


/* 
 * PROCEDURE: sysdiagramsDelProc 
 */

CREATE PROCEDURE sysdiagramsDelProc
(
    @diagram_id       int)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM sysdiagrams
     WHERE diagram_id = @diagram_id

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'sysdiagramsDelProc: Cannot delete because foreign keys still exist in sysdiagrams '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
go
IF OBJECT_ID('sysdiagramsDelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE sysdiagramsDelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE sysdiagramsDelProc >>>'
go


/* 
 * PROCEDURE: sysdiagramsSelProc 
 */

CREATE PROCEDURE sysdiagramsSelProc
(
    @diagram_id       int)
AS
BEGIN
    SELECT name,
           principal_id,
           diagram_id,
           version,
           definition
      FROM sysdiagrams
     WHERE diagram_id = @diagram_id

    RETURN(0)
END
go
IF OBJECT_ID('sysdiagramsSelProc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE sysdiagramsSelProc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE sysdiagramsSelProc >>>'
go


